3

Relational Model

I want to break the many-many relationship between SalesMan and Customer with the new entity: Sale. I would like to use idSalesMan and idCustomer as primary key in Sale but SalesMan has a foreign key that will be in the Sale entity too. Do I need to use all 3 foreign keys as primary keys or are idSalesMan and idCustomer enough?

Here is how the script might look like (using Postgres):

CREATE TABLE Company(
    idCompany SERIAL PRIMARY KEY,
    name VARCHAR(45) NOT NULL
);

CREATE TABLE SalesMan(
    idSalesMan SERIAL PRIMARY KEY,
    name VARCHAR(45),
    wage DOUBLE NOT NULL,
    Company_idCompany INT REFERENCES Company ON DELETE CASCADE
);

CREATE TABLE Customer(
    idCustomer SERIAL PRIMARY KEY,
    name VARCHAR(45) NOT NULL
);

CREATE TABLE Sale(
    SalesMan_idSalesMan INT REFERENCES SalesMan ON DELETE CASCADE,
    SalesMan_Company_idCompany INT REFERENCES Company ON DELETE CASCADE,
    Customer_idCustomer INT REFERENCES Customer ON DELETE CASCADE,
    PRIMARY KEY (SalesMan_idSalesMan, Customer_idCustomer 
    /*,SalesMan_Company_idCompany?*/)
);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Javier
  • 55
  • 9

2 Answers2

3

To answer the question in the tile: No, not necessarily.

A PK like that would only even make sense if the combination of FK columns needs to be UNIQUE and NOT NULL. Neither has to be the case. In fact, enforcing uniqueness in this model would seem very odd. Customers are not allowed to buy from the same salesman twice? (Involving the same company?)

You would have additional columns to make each row in Sale unique. A timestamp comes to mind. You would have that, but it typically doesn't make sales unique. Two purchases might happen at the same time. With no reliable natural key, use a surrogate PK like a serial or an IDENTITY column.

I would do that in any case, even if the set of FK columns happens to be defined as UNIQUE and NOT NULL. I would create a UNIQUE constraint or index additionally (and NOT NULL constraints) where applicable. (Or at least a plain index for performance in most cases.) Maybe with the exception of very simple cases with just two small FK columns that can form the PK and the table is not itself referenced by FK constraints and unlikely to change later. For anything else, a surrogate PK is simpler, faster, more reliable and makes later changes easier.

Also, like Mureinik explained, the FK column SalesMan_Company_idCompany is typically redundant and should be dropped: it's determined by the salesman. Unless your model allows sales between customers and salesmen involving an independent company somehow.

I would also reconsider the displayed naming conventions. More:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you all. Is there any reason why MySql Workbench (tool I use for modeling) generates the SalesMan_Company_idCompany foreign key in Sale? it does it automatically and if you remove the key it deletes the relation. – Javier Jun 16 '18 at 22:46
  • 1
    @Javier: I don't know about MySQL Workbench, sorry. Maybe not the best tool to work with Postgres. – Erwin Brandstetter Jun 17 '18 at 11:02
1

According to normalization best practices, you shouldn't even have a SalesMan_Company_idCompany column in Sale. The information about the company is held in the SalesMan table, and if you need it for a Sale, you should just join with the SalesMan table.

Mureinik
  • 297,002
  • 52
  • 306
  • 350