I have either had a major revelation with modeling or I'm really really lost. I have a current model that I have been working on in my spare time consisting of about 45 tables.
I have a single 15" monitor that I am working on to view all of these tables and it dawns on me that I am really in need of a huge flat screen or something. What do you guys use when you have a lot of tables to model?
I started looking at some of these tables and their relationships and it is really becoming unwieldly. I noticed where I completely forgot to relate 3 major tables to a parent table.
My question is:
I have tables that will contain data on several different types of people such as employees, customers, etc. Since they are all people and have a lot of the same attributes, *why* can't I use a single table and call it "players" or "people" and have a lookup table indicating their role in this db?
As far as I am concerned, all of these people have many of the same attributes with the exception of the role they play (Hence players). I'd just like to lump them into one table. It seems it would save me a lot of different relationships.
I could use other distinguishable attributes not directly related to "players" or "people" (Whatever you want to call it) and place those attributes into other tables with the PK from the players table.
I am thinking like superclass/subclass. What are your thoughts on this. Any insight is well appriciated. Thanks!
Pootle, yes, I read it yesterday. There are a lot of opinions in that thread and I'm not so sure that he was asking the same thing I am; or at least that's not what I am getting out of it, although it is similar.
Why should I have seperate addresses and phone numbers for example stored in seperate tables all over the place? Is it incorrect to have one table for all addresses, one table for all phone numbers, etc???
Pat posted in that thread the following:
Originally Posted by Pat Phelan
Having a "parent" table like inventory, with many potential "child" tables (workstation, server, printer, etc) is an advanced, but fairly common normalization technique called "entity decomposition" in Data Modeling. Is is directly comparable to the technique known as "sub-classing" in Object Oriented Design.
What you are doing is both possible and more importantly it is correct!
It seems that this is what I am trying to accomplish. No?
Is it incorrect to have one table for all addresses, one table for all phone numbers, etc???
no it isn't
except phone numbers are usually best handled as totally dependent attributes, rather than independent entitites
the only person who might care about a phone number as a phone number, independent of whose number it is, is the phone company
granted, if you have a many-to-many relationship, where the phone number is shared amongst several owners, then you might want to set up a many-to-many relationship table to track it -- i wouldn't, myself, i would simply have the phone number be a dependent attribute, and allow repetition
the same rationale can be applied to addresses, you could simply repeat an address if it is shared, although a separate table makes more sense because of the simplicity of being able to refer to a single address, which consists of multiple values (number, street, city, lat and long, etc.), using a single key value