Relational Database Standards

[-bc-]

I once attended a lecture on Database Design by a prominent bio-infomatics designer who started the lecture by saying "Any idiot can design a database... and MANY do."

In an effort to avoid being an Idiot I'm trying to review the Filemaker Standards and revisit design in general. It's clear to me that my last design was half-assed (as I look at it now). It is functional but not well designed and it leds me to a general question about the very basics of relational databases. What is the criteria for establishing a table?

Example: In my last database I created a table for "Clients", another "Relatives", another "Doctors" etc. I realize now that I should have created "People" with TOs of clients, relatives, doctors etc. because they all require the same basic information, name, address, phone... But what if some of these "People" have other single attributes associated to only that class that aren't tracked in the other classes of people, a diagnosis for instance (yeah there could be multiple diagnoses but assume there is only one). Do we establish a table of attributes unique to each class even if its a one to one relationship?

[comment]

That's a good question - and there is no single correct answer to it.

I realize now that I should have created "People" with TOs of clients, relatives, doctors etc. because they all require the same basic information, name, address, phone...

I disagree. The only valid reason to place all types in a single table is that they need to be treated the same - at least in some aspect, e.g. sending a birthday card. Otherwise two tables having the same attributes is merely a coincidence.

Continuing with your example, the purist approach would indeed prefer a daughter table for each sub-type, with a one-to-one relationship to the mother super-type table. However, having all possible fields in the one table is also possible - and works very well in many situations.

[David McQueen]

You may want to take a look at the book

Database Design For Mere Mortals by James Hernandez

Amazon link: http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840

It is a non FileMaker look at relational databases in general.

At the bottom of that link are some comments from users with other book selections.

In the end, in general terms, you probably want a good feel for:

Entities

Attributes

Relationships

Entity-Attribute diagrams

Entity-Relationship diagrams

Normalization - why you want it and how you will always fail to some extent.

HTH

Dave

[-bc-]

Thank you both for your helpful comments.

Hey Dave, I've been up your way a few times. Is Jerry's Fries still there? Best fries I've had since I was a kid