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


  • 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, 2012 at 6:32
  • it would be nice if you supply an entity relationship diagram
    – miracle173
    Aug 21, 2012 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, 2012 at 8:45
  • What does chain mean in this context? A chain of stores?
    – miracle173
    Aug 21, 2012 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, 2012 at 6:57

2 Answers 2


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.


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.

  • 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, 2012 at 11:58
  • Also, How did you add the diagram?
    – Shani
    Aug 26, 2012 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, 2012 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, 2012 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, 2012 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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