Organising an SQL DB

Cableguy

Expert
Licensed User
Longtime User
Hi guys...

I'm quite new to SQL and online DB... Actually zero experience...
But I have an Idea to an app wich will get 90% of on screen info from an online DB...
My issue is.... comments.

I imagine my app having only one or two tables... I think...
I will have to work those out after...
when I used Access DBS there was a "Key" field wich could be used across all tables of the DB... is it possible to do it with SQL too?

My main table will contain location info, but I want to link a "user comments" table by a "common" field like a key field...

Hmmmm am I making any sense here?
 

RandomCoder

Well-Known Member
Licensed User
Longtime User
W3Schools is always an excellent starting point for anything like this.
It's up to you how to arrange you're data but it is always good to have a unique ID field. Take for example a person table. It might contain first_name, last_name, and date_of_birth or DOB. To reference an individual person from this table you could select Joe Bloggs with date of birth 01/02/2003 to ensure that you had the correct Joe Bloggs (it's a very popular name, at least here in the UK). But it is better to create a unique ID for each person. You then only need to store this ID in your other tables i.e. Purchases to know exactly which person to get the details for. You can also change a persons last name for example and it doesn't break the relationship with other tables.

Here's how to create an auto incrementing unique ID...
http://www.w3schools.com/sql/sql_autoincrement.asp
 

dlfallen

Active Member
Licensed User
Longtime User
Hi Cableguy,

Yes, you are making sense - the hardest part of learning anything new is learning the terminology, but you expressed yourself well.

You received excellent advice from RandomCoder. I just thought I would give you one example. I have a Major League Baseball Database that contains three tables: Master, Batting, and Pitching. The Master table contains static information about each player (name, date of birth, etc) along with a unique player ID. The Batting and Pitching tables contain season by season statistics, each entry contains the player ID for that record. Here is my query for pulling Babe Ruth's batting statistics:

SELECT Master.Rowid as _id, [Master].[nameLast], [Master].[nameFirst] FROM 'Master' JOIN 'Batting' on [Master].[playerID] = [Batting].[playerID] where Master.nameFirst LIKE "Babe" and Master.nameLast LIKE "Ruth"

Good luck, it's always fun learning new things!
 

RandomCoder

Well-Known Member
Licensed User
Longtime User
I don't know if anyone here is familiar with MS Access and I've not used the latest versions but, there was and probably still is a very useful tool for creating queries. It was basically drag and drop table names and required fields into a grid. Enter the criteria's such as WHERE name=Joe and MAX records and/or sort order then it was possible to run the query and see the results that were returned. Best of all, you could change the view to show the SQL statement that was created. This is an excellent tool to have and it's how I've learnt to create some pretty complex queries.

Is anyone aware of a similar tool that allows selection of the tables and fields in a simple visual grid where various criteria can be set and it churn out the SQL statement? It might be useful for @Cableguy and others?
 

RandomCoder

Well-Known Member
Licensed User
Longtime User
A quick search of the web and this is the sort of tool I'm referring too (I'm not endorsing this one, I've never used it!). Is there a better tool that someone can recommend?
 

RandomCoder

Well-Known Member
Licensed User
Longtime User
Sorry to have spammed this thread but I've searched a little more and here is the open source tool I reckon I'll be using (unless someone has a better idea).
http://sqleo.sourceforge.net/
It's Java based and so cross platform to :D
 

Cableguy

Expert
Licensed User
Longtime User
Thank you both for all the good advices, especially @RandomCoder for the time you spent on your queries!!
It will take me a whole to get the app off the paper... and even more to put it there!
But now I do have a clue on how to proceed
 
Top