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:

c_c_key
s_s_key
m_m_key.

for each table, preface each field in the table with the table’s prefix:

c_cocktail_name…
s_sauce_name
m_meat_name

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_cocktails
———–
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_meats
———–
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_sauces
———–
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
prefix_prefix_key.
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.

__s_i_m_p_l_y__t_i_m_o_t_h_y__

Advertisements

Posted on October 22, 2005, in programattical. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: