database structure design

what would be the best approach?

[trismegisto]

i’m building a new database. i have to display in a page some information about physicians. a simple table will do the job; ok, no problem… somewhere in this information i have to display 5 different ways of contacting the physician: phone 1, phone 2, cel, beeper & email. but most of the physicians only have phone 1, cel and email; just 8 or 9 have all the 5 ways of contacting available; some just have one phone; another group just phone and beeper, and so on. i don’t know what would be the best: build the database with the 5 fields for the 5 different ways (although most of the columns in most of the rows wouldn’t have any data. which brings another question: having a complex structured database with almost any data isn’t a problem?); or would be better to just have a ‘contact’ field in which i would put whatever ways possible, just separated by a line?

i don’t know what would be the best approach: a field containing all the ways, or 5 fields for every way, although most would be empty. someone with experience with this, please some advice for a beginner!

[Mardi_Gras]

I would go with all the numbers in different cells - it will make any sort of lookup much easier. If you must combine the info into fewer cells, maybe you could go with phone, cell phone, and other - and lump the odd numbers into "other."

[trismegisto]

ok. i’ll do it as you say. thanks for your advice! i think is not so much problem having empty recordsets in your database…

[seindal]

I'd make a separate table for contact info, with type and physician_id fields, so you can have any number of contacts. Some day you need more than what you can foresee anyway, so you can just as well put it in now.

René.

[trismegisto]

hey seindal, that seems even better, you mean linking the contact table with the physician table with the id field?

[seindal]

Yep. That's normalisation. You have to normalise your database when you design it, in order to avoid redundance and maintain a dynamic structure.

Every time you find many records in a table with identical values, you should consider factoring that field into a separate table.

In your case the central table will be physicians, but any kind of auxilliary data associated with physicians might be places in several other tables. What if several physicians share a studio? Share telephone and secretary? Some might share one phone number, and still have some of their own?

So if you have a separate contact table, will the relationship with physicians be 1-1, 1-N or M-N.

A 1-1 relationship is easy. You just add fields to you central table.

A 1-N relationship is expressed with a secondary table with the data, and an index pointing into the central table. This could be a physician_id in a contacts table.

A N-M (many to many) relationship is done with two data tables, each with a unique id, and an auxiliary table with the two ids to match.

If you want to represent several physicians sharing a common phone number, you have a N-M relationship, since any physician can have many contacts and a contact can be associated with several physicians.

Don't be afraid to make several tables. An SQL database is optimised to make joining tables fast.

Another thing. When you design databases, always add a numeric primary key field to each data-carrying table, and reference records with this id. It makes things easier and faster.

Unless you're really sure you know how your dataset will develop and be used in the future, you should design with maximum flexibility in mind, which translates into database normalisation.

There is a short tutorial on normalisation on
[databases.about.com...]
Go for 3NF.

René

[trismegisto]

thanks for your answers! i’ll always build a database with normalization in mind from now on. i think i’m getting it… if i have one field in my table that might have multiple values (like contact or patients) on every record, i should make a new table relating the data. if i have, say, 4 or 5 fields with data that might be associated among them (phone_1, phone_2, email, fax, beeper, etc; all of these contacts), i should also make a new table for that related fields. thanks for the advice, it’s very valuable.

[bcc1234]

Look up any info on relational database theory and data normalization. It will help you in the long run BIG TIME.