Data Modeling Question

Question

Data Modeling Question

michael.rosqvist

Old Hand

Points: 379
More actions
January 20, 2005 at 3:26 am

#92110

I am design a database containing BrokerOffices Brokers and
BrokerAssistants.
(among other things)
I do three tables: BrokerOffices, Brokers and BrokerAssistants. All have a
1:M relationship
Since an assistant is assitant for one or many brokers I do a table
Assistancies containg this M:M relationship
(Assistants and Brokers must belong to the same Office)
My boss says:
Do one table called Parts containg BrokerOffices, Brokers and
BrokerAssistants.
Do a Relationstable containing the necessary relations between these
entities.
Do one table called Roles containg these three roles and relate it to the
Partstable.
This is THE WAY TO DO IT.
I say my boss is wrong.
What do you think?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AKM SSCommitted Points: 1538 More actions · Answer 1

Sometimes it is best to do what the boss says even if we think it's wrong

But seriously, from the way yuo describe it, I would have to agree with you. One of the principles of normalization is that a single table contains information about ONE type of entity. I don't know exactly what BrokerOffices or Brokers are, but if the former are locations like London, Paris, New York, and the latter are people like Mr Smith, Mr Jones, then they have absolutely no business being in the same table.

If that were the say we did things, any database would have just one table, with a 'type' column to tell us what type of entity each row described! Ask your boss if that's what he wants

Antares686 SSC Guru Points: 125444 More actions · Answer 2

First off from your naming convention should make table names singular.

So instead do something like this

Office -- I would change from BrokerOffice because even if other positions are in here you still should be using a common list of offices.

Broker

Assitant

Now the question I have is do multiple Assistants support the same broker? It sounds like this is the case but I want to confirm.

Also what info is in the Broker and Assistant table and can an Assistant become a Broker at any point in their career and vice versa?

Or for design sake what is in the Offices table and is it 1 office to many brokers and assitants or 1 broker and/or assitants to many offices and could it potentially change?

Now as for intergrity of the data another table may not work like he thinks.

My impressions are that instead you should use a trigger to validate that both the broker and assitant are in the same office. This is a condition and needs to have a constraint on it but basic constraints aren't complex enough to handle. Plus there is no need for an extra table if you do backend validation thru a trigger and a custom error can be raised for this scenario.

michael.rosqvist Old Hand Points: 379 More actions · Answer 3

1. I prefer (the Celko way) plurals since a table in most cases contains many entities.

2. I prefer specialization in tables and dataelements because otherwise it is quite difficult to build a repository. And BrokerOffice is a subtype of Office.

3. Yes, an assistant can assist many brokers. And vice versa.

4.An Assistant can be a broker. In that case the assistant gets deletet and a broker is born.

5. An assistant/broker is always employed of one and only one office. A person can be a broker on many offices, he is uniquely defined by his employyenumber and employernumber.

6. Why do you thin a trigger is better than foreign key constarints?

/m

Antares686 SSC Guru Points: 125444 More actions · Answer 4

1) Sorry brain is working backwards today that is the third time and I usually follow the rules well.

2) Ok

3-4) Thanks.

5) So how do you id which branch or branches a Broker and Assitant are actually available at?

6) Let me think about everything still 5 will give me an idea.

Can you also post your current DDL and the relationship info, that would be useful.

MarkG Ten Centuries Points: 1217 More actions · Answer 5

Sounds like you've already gotten some good advice. I would suggest that you might want to make your Broker & Assistant tables date sensitive, i.e., with a StartDate and EndDate.

This way you don't loose any data when an Assistant is promoted to a Broker status you just record the EndDate as an Assistant and his new Broker record will have a StartDate on the next day.

Now, if you want to keep a dated listing of all Broker/Assistant assignments you could do that in a separate table with constraints to the Broker & Assistant tables. That way you can keep track of questions like "what Assistants did Bill have last year?" Or the fact that Jeff worked for Bill on three separate date ranges.

It all depends on where you want to go with all this power and knowledge.