table designs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

table designs

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

That would kind of defeat the purpose of using a database :wink:
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Sorry for the late reply, thanks for the input.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
Post Reply