Database design tip of the day..
Instead of actually designing my database today, I think I will drink a few sazeracs, make some Basil Pesto, watch some football and fry up the chicken I cut up two days ago and left sitting in some Cajun Seasoning and put that bad-boy in some Sauce Piquant that has been mellowing since Wednesday.
That said, here is a handy database design tip.
Lets say you have 3 tables: Cocktails, Sauces and Meats.
Try this naming convention and see if it makes sense to you.
Name them c_cocktails, s_sauces and m_meats.
for each table, make it’s primary key of the form: prefix_prefix_key.
For example the primary keys for the three tables are:
for each table, preface each field in the table with the table’s prefix:
for each foreign key in a table, use the form ThisTablesPrefix_ThatTablesPrefix_key
so, the primary keys and foreign keys for each of our tables would have this form
c_c_key ; primary key for c_cocktails
c_m_key ; foriegn key to m_meats
c_s_key ; foriegn key to s_sauces
c_cockatail_name ;data column
m_m_key ; primary key for m_meats
m_c_key ; foriegn key to c_cocktails
m_s_key ; foriegn key to s_sauces
m_meat_name ;data column
s_s_key ; primary key for s_sauces
s_c_key ; foriegn key to c_cocktails
s_m_key ; foriegn key to m_meats
s_sauce_name ;data column
Now, you ask, what is the point?
Well, I answer, there are several.
First, when you are coding, when you look at a field and you wonder what table it is in, you
know by looking at the prefix: the s_”whatever” field is in the s_”something” table, the m_”whatever” field is in the m_ table and the c_”whatver” field is in the c_ table.
c_cockatail_name is in the c_ table
m_meat_name is in the m_ table
s_sauce_name is in the s_ table
Second, you can immediataly identify a table’s primary key–they are always of the form
c_c_key, is in the c_ table and is a primary key
m_m_key, is in the m_ table and is a primary key
s_s_key is in the s_ table and is a primary key
Third, you know what table to look in when you see a foriegn key (very useful) since it is always of the form ThisTablesPrefix_ThatTable’sPrefix_key.
c_m_key ; is in the c_ table and points to the m_ table
c_s_key ; is in the c_ table and points to the s_ table
m_c_key ; is in the m_ table and points to the c_ table
m_s_key ; is in the m_ table and points to the s_ table
s_c_key ; is in the s_ table and points to the c_ table
s_m_key ; is in the s_ table and points to the m_ table
Now, when you get to hundreds of tables, you will get namespace collisions, so use your imagination on table names.
Hope it helps.