Have I understood Database design correctly?

views:

110

answers:

Have I understood Database design correctly?

Have I understood db design correctly?

A user can have one or multiple professions
Each profession has multiple qualifications associated with it
A user can have none or all of these qualifications.

Are there any improvements that should be made? simplicity should be maintained. I intend to shorten table and column names.

alt text

UPDATE: is this better? alt text

+4 A:

No-no, do not shorten column or table names. There's nothing worse than usr_qlfctns or prfsn2qlfctns.

Otherwise, your design seems fine.

Anton Gogolev 2010-10-22 10:54:35

thanks, I originally was going to keep the names as they for readability, but my friend said how much of a pain it can be typing full length, and misspelling eg 'professions' and 'profesions' instead of example 'profs'

davivid 2010-10-22 11:18:31

@davivd it can be worse trying to remember whether you called it prof, profs or prfsns :)

Remou 2010-10-22 11:54:39

But it helps to spell the table name correctly :-) Qulaification [sic]

Tim 2010-10-22 12:05:08

haha. well spotted.

davivid 2010-10-22 12:29:19

+1, it is virtually impossible to memorize shortened column names in a database with say 40 tables! I would also suggest using camleCase instead of underscores to separate words; I've always had to use the later, but it is such a pain typing out all those underscores!

Stephen Swensen 2010-10-22 12:49:28

your friend needs to be introduced to real DBA tools that do autocomplete and syntax checking for you, do NOT shorten names for the reasons your friend is saying. having the complete table name in foreign keys is very important for maintenance by your self later or other people. Also CamelCase is NOT appropriate, since SQL identifiers are case insensitive, stick with the _UNDERSCORE_ separators.

fuzzy lollipop 2010-10-22 13:56:00

what DBA tools would you suggest?

davivid 2010-10-26 10:06:54

@fuzzy, unless the database engine (lower/upper)case on identifier creation there´s no problem in using CamelCase. This is a problem some engines, other's simply compares with case insensivity. For logic design, makes no difference. Must have attention on physical structures design to not force the dba/developer to double quote everything.

Fabricio Araujo 2010-10-28 17:22:06

for logical models, it doesn't matter, but for physical models it does matter case, the accepted practice is to name things all lower case with _ separators for max portability

fuzzy lollipop 2010-10-29 03:32:06

If "no a user cant have a qualification unless he has a profession" then maybe the user_qualification should link to both user_id and profession_id in user_professions to enforce that the combination user and profession is there before a qualification is set.

David Mårtensson 2010-10-22 11:51:19

like as in the update above?

davivid 2010-10-22 12:27:06

@davivid - Actually, i wouldnt do it as per the updated diagram you have shown. I personally would do it by having user_qualifications having a composite foreign key to profession_qualifications on columns profession_id + qualification_id. That is in addition to what i think is already a FK from User_qualifications to User_professions on user_id + profession_id. I dont quite understand how your updated design stops an invalid combination from being entered in user_qualifications.

InSane 2010-10-22 13:11:41

Cool thanks, I decided to use a better software - Have I understood correctly? with the updated diagram above?

davivid 2010-10-26 10:06:09

ansaurus

tags:

views:

answers:

Have I understood Database design correctly?

related questions