Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are planning a new system with a new DB (MS SQL Server).

We have the following entities and the following relations:

Entities: Clubs, Chains, PaymentTypes, etc...

Relations: Clubs-Chains (M-M); (Clubs_Chains)-Commission (1-1); Clubs-Chains-PaymentTypes (M-M);

  • Commission is a numeric field containing perecentage value , and is not an entity.

We would like the DB to be normalized. We thought about the following option, but we are not sure if it is the best solution for the scenario:

Clubs_Chains table: ClubChainID PK int not null, ClubID FK int not null, ChainID FK int not null, Commission int

Clubs_Chains_PayMethod table: ClubChainID PK int not null, PaymentTypeID PK int not null

Thanks!

share|improve this question
    
It seems to me that your naming is rather inconsistent. your entity names are plural (Clubs) and singular (PaymentType). The name of the ID filed contains a plural (ClubChainsID) or a singualr(ClubId). I think it would be simpler to remember the names if you follow some simple naming conventions. –  miracle173 Aug 21 '12 at 6:32
    
it would be nice if you supply an entity relationship diagram –  miracle173 Aug 21 '12 at 7:08
    
Hi @miracle173 , thanks for the suggestion, I changed the naming, hopefully there is an improvement... How would you suggest to add the relational diagram to the question? –  Shani Aug 21 '12 at 8:45
    
What does chain mean in this context? A chain of stores? –  miracle173 Aug 21 '12 at 14:39
    
@miracle173, yes a chain of stores. A chain can be connected to one or many clubs and vice verse, and for each Chain-Club Connection there is a percentage commission, and at least one PaymentType. –  Shani Aug 23 '12 at 6:57
add comment

2 Answers 2

up vote 7 down vote accepted

It isn't necessary to have a surrogate primary key on your clubs_chains table.

The combination of the two foreign keys in clubs_chains is adequate for the primary key.

You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key. This might be helpful since it would allow more direct joins between clubs_chains_paymethod and your clubs and chains tables, without sacrificing any referential integrity.

On the other hand, some people just love using a surrogate primary key, even on intersection tables. If that is your data modelling style, then it's OK too.

share|improve this answer
add comment

An ER diagram I deduced from your descritption. I am not sure if this is a valid interpretation of your description. ER diagrams and the transformation of ER diagrams to relations are discussed in this article

ER diagram created with dia

For the relations and entities labeled with ??? I did not know an appropriate name.

share|improve this answer
    
This seems correct, (Except for the '0' between the combined entity and the relation to 'PayMethod', which should be '1'). What would you suggest to do when translating this into tables? Are there any readings that you can recommend on how to translate the relations into tables? Thanks –  Shani Aug 23 '12 at 11:58
    
Also, How did you add the diagram? –  Shani Aug 26 '12 at 7:49
    
i created the diagram with dia exported it as jpeg. This I added to my post by using the image button (Ctrl+G) –  miracle173 Aug 26 '12 at 15:55
    
the translation is simple but currently i did not know any readings. if i have more time i will search the web for an appropriate article. you can put a question in this forum if sombody knows a good article about using entity relationships diagrams. –  miracle173 Aug 26 '12 at 16:03
    
did you know approprate nouns/verbs for the entities/relations labelde with '???' ? Then I would icorporate them int the diagram. –  miracle173 Aug 26 '12 at 16:05
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.