3

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.

0

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.

  • Could you please fix your typos? Also, use formatting to make your post easier to understand. – dezso Apr 13 '17 at 15:50
  • What do you mean by typos. Not everybody native language is English ! – Harry Leboeuf Apr 14 '17 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.