When to create a new table if functionality is the same

database-designschema

I am having trouble making database schema/design decisions. The database I am building is relational.

Consider this example:

I have these tables: orders, products, companies.

I want to create links between two orders, two products, and two companies (to show that they are related). There is no additional data about the relationship which needs to be recorded, just the fact of the relationship itself (i.e. the relationship table just needs the two IDs and not any other fields – What I mean by this, is that there is no meta data or other fields for the relationship, I purely need to know that a relationship link exists, thus only need an object_one_id, and object_two_id).

I can create 1 table (relationships), or I can create 3 tables (order_relationships, product_relationships, company_relationships). If I just use 1 table, I will need a separate column to determine the relationship type (product, order, or company), and will need to look up the related table based on the object type. If I use 3 separate tables, then I can create a direct relationship between the foreign keys and primary keys across the tables (for example product_relationships would have a product_id, and related_product_id, which both link directly to products in the products table).

Here is role information:
For products, a product can have multiple other products to which the primary product is a comparable. This means that these other, related products, are considered the same physical makeup.

For companies, a company can have multiple other companies to which the primary company does business with. This means that these other, related companies, are considered vendors or customers.

For orders, an order can have another order to which the primary order correlates to. For example, if a company creates an invoice/sales order, then another order is created for the customer company as purchase order.

How can you decide which solution is better? What other questions would you ask to make the decision?

For the DBMS, I am looking to use either MySQL or MariaDB.

Best Answer

Quit thought (and MS SqlServer background). Your order table will already have multiple tables, Header, Details. You could 'link' your OrderDetails with the products directly... Ok al basic design, but for Statistics and Query Oprimizers these direct links are gold, if yoy work with a relations table you cannot create a foreign key from that table to all tables as you don't know what parent value it will hold. The parent could be a order, customer, product en the detail also order, customer, product, the kind of relation would be specified in a field. Works fine for querying, that's not the issue, but on performance you would loose a lot i'm afraid.

Related Solutions

Sql-server – Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

I'd question why you have OrdersForProductX tables at all
It's possible the FK problem you've asked about can be designed out...

If these tables have the same structure, then you simply need a ProductType column on some OrderProduct table. Then Payment just links to that with one FK

If the table have different structures, I assume they have some common attributes. So, you can have a common OrderProduct table then specific child table per product type (see below) Again, Payment just links to the commone table with one FK

This is the "superkey/subtype pattern"

UQ1 is the "super key" used a foreign key on the subtype tables
Each subtype table has a composite PK and FK on (OrderID, ProductType)
Each subtype table has a CHECK constraint to restrict types in that table

OrderProduct

OrderID, PK, UQ1
ProductType, UQ1
CommonThing1
...

OrderProductA

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = A
ProductAThing1
...

OrderProductB

OrderID, PK, FK
ProductType, PK, FK, CHECK ProductType = B
ProductBThing1
...

How to relate two rows in the same table

What you have designed is good. What needs to be added is a constraint to make the relationship directionless. So, you cannot have a (1,5) row without a (5,1) row being added as well.

This can be accomplished^* with a self referencing constraint on the bridge table.

*: _{it can be accomplished in Postgres, Oracle, DB2 and all DBMS that have implemented foreign key constraints as the SQL standard describes (deferred, e.g. checked at the end of transaction.) Deferred checking is not really needed anyway, as in SQL-Server that checks them at the end of statement and this construction still works. You cannot do this in MySQL because "InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row".}

So, in Postgres the following will match your requirements:

CREATE TABLE x
(
  x_id SERIAL NOT NULL PRIMARY KEY,
  data VARCHAR(10) NOT NULL
);

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    FOREIGN KEY (x_id2, x_id1)
    REFERENCES bridge_x (x_id1, x_id2)
);

Tested at: SQL-Fiddle

If you try to add a row (1,5):

INSERT INTO bridge_x VALUES
(1,5) ;

It fails with:

ERROR: insert or update on table "bridge_x" violates foreign key constraint "x_x_directionless"
Detail: Key (x_id2, x_id1)=(5, 1) is not present in table "bridge_x".:
INSERT INTO bridge_x VALUES (1,5)

Additionally, you can add a CHECK constraint if you want to forbid (y,y) rows:

ALTER TABLE bridge_x
  ADD CONSTRAINT x_x_self_referencing_items_not_allowed
    CHECK (x_id1 <> x_id2) ;

There are other ways to implement this as you mention, like storing only one direction of the relationship (in one row, not two) by forcing the lower id in x_id1 and the higher id in the x_id2 column. It looks easier to implement, but usually leads to more complex queries later:

CREATE TABLE bridge_x
(
  x_id1 INTEGER NOT NULL REFERENCES x (x_id),
  x_id2 INTEGER NOT NULL REFERENCES x (x_id),
  PRIMARY KEY(x_id1, x_id2),
  CONSTRAINT x_x_directionless
    CHECK (x_id1 <= x_id2)                       -- or "<" to forbid `(y,y)` rows
);

Best Answer

Related Solutions

Sql-server – Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

How to relate two rows in the same table

Related Question