This is what's referred to as an entity type hierarchy in data modelling
terms. The "proper" way to model these is to create a supertype table that
contains attributes that are common to all members, and then to create
multiple subtype tables that contain information that is specific to a
particular type of member (e.g., college graduates). Each of these tables
will have the same primary key (e.g., MemberId), so you can join as many of
them as you like.
If it turns out that you have a lot of common attributes (that apply to all
members), and only a few specific attributes, it is not uncommon to just
create one big table to hold everything.
>From a programming perspective there is very little difference to the two
approaches (except that you avoid joins with the latter approach), so it's
really an issue that a DBA should decide.
Hope that helps,
Bob
-----Original Message-----
From: Jason Lotz [mailto:[EMAIL PROTECTED]]
Sent: March 20, 2001 11:52 AM
To: CF-Talk
Subject: DB structure advice
This is more of a theory question than an actualy request for help. Let's
say you are building a database of members with lots of information that
applies to every member (name, address, phone, etc.) This could be about 40
seperate fields. Then, on top of that, each member can have lots of
optional information that can easily be grouped together. For example, if
they graduated from college, you want to store their degree information or
if they own a company, you want to store their company info. Each of these
"optional groups" are one-to-one (each member can only have one chunk of
this data but doesn't have to have it at all.) Now here's the question -
When you are building this database, you could easily put it into one huge
table because all the information is one-to-one. However, it's a pain when
you only want to get the "degree" information or the "company" information.
>From a programming standpoint, is it best to break these into smaller tables
for ease of use or to keep it into one table?
Thanks,
Jason
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
