Which tables to create according to relations

Question

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!

Answer 1

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.

Answer 2

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

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