I am having trouble making database schema/design decisions. The database I am building is relational.
Consider this example:
I have these tables:
I want to create links between 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 (
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
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.