Im sure weve all done it. Started to write a select statement and then had to alt+tab back to enterprise manager to find out the column name. Well, if your still starting out in SQL and having trouble organizing things, this method is for you. And its all logical, so recalling table and column names should be a breeze.
Ok, to start of with were going to make a small list of all our tables in alphabetical order, leaving out any link tables you may need. For my example Im using a small discussion forum database.
Tables.
-------
Forum
Msg
User
Topic
Now, next to all of these create a 2 character reference code for each table, you can use three if it makes much more sense.
Tables.
-------
Forum -=- FR
Msg -=- MS
User -=- US
Topic -=- TP
Ok, now we have those, we can make out link tables. We need to link the users to topics, and it makes more sense to link the ID's through a link table, so we combine the two codes.
Link Tables.
------------
User & Topic tables -=- US_TP
Now we have all our table names, go ahead and create them all, using there long names(i.e. 'user' table). Once there made, we can start entering fields, I wont go through them all, just one example.
'User' Table Columns.
--------------------
US_ID
US_USERNAME
US_PASSWORD
Now as you can see we use our two character code as a suffix to each column. This doesnt do much in the 'user' table, but take a look at the topic table.
'topic' Table Columns(shortened).
----------------------
TP_ID
TP_TITLE
TP_TIMESTAMP
US_ID
You can see weve taken the column name from the 'user' table, and just used it in the topic table. So if you need to remember of the top of your head what the link from the topic table is to the use table, its as simple as thinking, user table is US, so the link column is US_ID. This works for near enough any simple database, and works on link tables to, for example,
'topic' And 'user' Link Table.
------------------------------
Table name -=- US_TP
US_TP_ID
US_TP_EMail
US_ID
TP_ID
You can easily figure out where any of those columns link, using there code. Ive attached a quick mock up of the database Ive discussed here in JPG format. As you can see it works well in diagram mode as well!
|
By 6 Users
5/26/2006 10:29:55 PM: 
