Is this database design many-to-many correct for production?

Question

I have a question about implementing simple relationship between four tables.

Client
Employee
Automobile
Sales

Created them all but I just want to be sure I am not missing something that can harm the db design.

The sales table is the mediator between all the other tables. Here is the SQL

CREATE TABLE automobile (
    automobile_id           INTEGER NOT NULL,
    automobile_brand        VARCHAR2(16),
    automobile_model        VARCHAR2(16),
    automobile_year         DATE,
    automobile_color        VARCHAR2(12),
    automobile_kilometers   INTEGER,
    automobile_price        NUMBER
);

ALTER TABLE automobile ADD CONSTRAINT automobile_pk PRIMARY KEY ( automobile_id );

CREATE TABLE client (
    client_id          INTEGER NOT NULL,
    client_name        VARCHAR2(25),
    client_address     VARCHAR2(32),
    client_telephone   VARCHAR2(15)
);

ALTER TABLE client ADD CONSTRAINT client_pk PRIMARY KEY ( client_id );

CREATE TABLE employee (
    employee_id          INTEGER NOT NULL,
    employee_name        VARCHAR2(25),
    employee_position    VARCHAR2(15),
    employee_telephone   VARCHAR2(15)
);

ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY ( employee_id );

CREATE TABLE sales (
    sale_id         INTEGER NOT NULL,
    client_id       INTEGER,
    automobile_id   INTEGER,
    employee_id     INTEGER,
    sale_date       DATE
);

ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY ( sale_id );

ALTER TABLE sales
    ADD CONSTRAINT automobile_id FOREIGN KEY ( automobile_id )
        REFERENCES automobile ( automobile_id );

ALTER TABLE sales
    ADD CONSTRAINT client_id FOREIGN KEY ( client_id )
        REFERENCES client ( client_id );

ALTER TABLE sales
    ADD CONSTRAINT employee_id FOREIGN KEY ( employee_id )
        REFERENCES employee ( employee_id );

ALTER TABLE sales
    ADD CONSTRAINT automobile_id FOREIGN KEY ( automobile_id )
        REFERENCES automobile ( automobile_id );

ALTER TABLE sales
    ADD CONSTRAINT client_id FOREIGN KEY ( client_id )
        REFERENCES client ( client_id );

ALTER TABLE sales
    ADD CONSTRAINT employee_id FOREIGN KEY ( employee_id )
        REFERENCES employee ( employee_id );

and the ER diagram

Can somebody who is aware of DB design share opinion? Thank you so much!

Answer 1

The scripts for the foreign keys for automobile, client and employee seem to be duplicated.

The names of the constraints should contain the names for FK and the PK tables, e.g.

fk_sales_client

That way they will not conflict with other constraint names when the DB schema grows and new constraints are added. E.g. you might have an employee who is the responsible contact person for a client. With your naming schema you end up having two constraints called employee_id (for client->employee and sales->employee).

Sometimes more than one foreign key to the same PK-table exists in the same table, e.g. in a products table you might have a FK to the manufacturer address and another to the supplier address. Then you need another way to differentiate the constraint names

fk_product_address01
fk_product_address02

or

fk_product_address_man
fk_product_address_sup

---

If one client can buy more than one automobile per sale and those can be diffferent automobile models, then you must refacor your DB. You need a new connection table sales_item to realize a m-to-n relation between sales and automobile. Probably this new table will also contain a quantity column.

+------------+     +------------+
|            |     |            |
|  Client    |     |  Employee  |
|            |     |            |
+----+-------+     +---------+--+
     ^                       ^
     |                       |
     |     +--------------+  |         +-------------+
     |     |              o--+         |             |
     +-----o  Sales       |            |  Automobile |
           |              |            |             |
           +----+---------+            +-------+-----+
                ^                              ^
                |                              |
                |        +------------+        |
                |        |            |        |
                +--------o Sales_Item o--------+
                         |            |
                         +------------+

Answer 2

Yes it is true from your current database design only one automobile is purchased per sale

to overcome this limitation you can split your sale table in two

CREATE TABLE sales (
    sale_id         INTEGER NOT NULL,
    client_id       INTEGER,    
    employee_id     INTEGER,
    sale_date       DATE
);

CREATE TABLE sale_items(
    item_id         INTEGER NOT NULL,
    sale_id         INTEGER NOT NULL,   
    automobile_id   INTEGER NOT NULL,   
);

Here item_id can be either autogenerated or a serial number per SALE

If you decided to have item_id as autogenerated then your primary key on sale_items can be

ALTER TABLE sale_items ADD CONSTRAINT sale_items_pk PRIMARY KEY ( item_id );

if item_id is unique only per sale then

ALTER TABLE sale_items ADD CONSTRAINT sale_items_pk PRIMARY KEY ( sale_id, item_id );