Defining proper relationships between similar entities
Date: 22 Jan 2007 06:44:38 -0800
Message-ID: <1169477078.218861.284680_at_v45g2000cwv.googlegroups.com>
Consider a scenario in which various users must be stored in a relational database. Said users have different access levels. In addition, each type of user has different information associated with their account. Here's an example:
Customer
{
Username
Password
FirstName
LastName
Address
PhoneNumber
EmailAddress
}
SupportPerson
{
Username
Password
FirstName
LastName
PhoneNumber
}
Administrator
{
Username
Password
EmailAddress
}
Based on business requirements, all of these users have a logon (username/password pair) in common, and some have a first/last name in common as well. Generally speaking, I find that this sort of asymmetry amongst related entities tends to surface rather frequently. My initial thought was to have a single denormalized table to represent all possible user types, but there are cases in which there is so great a difference in the amount of fields each entity has, it just seems wrong to have all those empty spaces. It would be nice to hear from some professionals on how this is handled as I'd like to avoid passing on the burden of maintaining a poorly designed database. Received on Mon Jan 22 2007 - 15:44:38 CET