Page 1 of 1

table designs

Posted: Sun Jul 01, 2007 9:42 pm
by John Cartwright
Okay this has come up several times in the past couple years, and I've always kind of taken to easy way out, but enough is enough ;)

I've been faced several times when a project I'm working on would involve several types of users, all requiring different kind of details stored about them. To keep things simple, I'll use a single modeling website I'm working on. The types of users include: studios, males, females

Now what I've done is created 5 tables,

accounts -- stores username, password, created datetime
account_details -- commonly details such as name, phone number, email address, mailing address

account_profile_studios -- furtur profile details exclusive to this category, such as company name
account_profile_males -- etc
account_profile_females -- etc

This seems to work OK -- however this results in some duplication of field names in the profile pages simply because females and males have common attributes, but it is not the case with studios. For example, height is exclusive to male and female and not studio, so it cannot go in the account_details table. Therefore, I would be forced to have height in both males and females.

I know some might say that is acceptable, but when you have 10+ fields that are common between to fields it gets ugly. That just smells bad to me.
Any thoughts on how I can rearrange this database structure to prevent any duplication?

Posted: Sun Jul 01, 2007 9:46 pm
by superdezign
If it really bothers you, you could compact all of profile data into one field: data.

The `data` field would hold a serialized array with the user information. Granted, a serialization error will totally destroy a user. :P

Posted: Sun Jul 01, 2007 9:48 pm
by John Cartwright
That would kind of defeat the purpose of using a database :wink:

Posted: Sun Jul 01, 2007 10:18 pm
by Benjamin
Create an account_profile_attributes table which contains any field that is currently being duplicated in the male and female tables.

It should have a primary key corresponding to the auto_increment field in the accounts table, as should the male and female tables.

Posted: Mon Jul 02, 2007 3:19 am
by CoderGoblin
I would create 2 tables.

Attributes : consisting of attr_id, attr_name, attr_desc.
User2attr : consisting of simply the user_id, attr_id's and the value (text). (Where user_id=1, attr_id=many).

Doing this would make joins easy and allow flexibility in future. To add a new attribute simply add a new row in the attribute table and create rows in the user2attr table if necessary.

Posted: Mon Jul 16, 2007 2:52 pm
by John Cartwright
Sorry for the late reply, thanks for the input.

Posted: Mon Jul 16, 2007 3:24 pm
by califdon
Well, okay, you got my attention. Having taught database classes in college for nearly a decade, as well as having developed many commercial database projects and several personal ones, I come at the task from a more rigorous point of view than perhaps some of you. To me, designing the schema for a project is never a matter of "this seems good" or "let's try this approach." The formal way to decide these matters is well documented in books and is based on relational database theory, as formulated by E. F. Codd and written about by Chris Date (from whom I took a seminar, some years ago).

It all starts with identifying appropriate entities and their properties. Each entity becomes a table, in which the properties become the fields (or columns). Sometimes there may be an argument to be made for denormalizing the resulting schema for purposes such as performance, but I have found that to be an uncommon need.

So in this example, the entities that I would identify would be accounts, people and studios. Male/female is merely a property of a person, not a separate entity (unless, possibly, in a medical application). A studio is clearly a different entity from a person. An account may point to either a person or a studio. Seems very simple to me.

That leads to something like the following:

tblAccounts
  • AcctID (PK)
    UserName
    Password
    UserType
    DateRegistered
tblStudios
  • StudioID (PK)
    AcctID (FK)
    StudioName
    Address
    ...etc.
tblPersons
  • PersonID (PK)
    AcctID (FK)
    Lastname
    Firstname
    Sex
    Address
    Phone
    ..etc.