Supplier-customer relationship in the same table

Question

I'm no MySQL expert and I have to design a rather complex db for my level.

The problem I'm facing now consist in having a supplier-customer relationship within the same table (macro categories of companies):

Macro table

id    name          mega_id    macro_customer_id
------------------------------------------
1     Furniture     2          2,4,5,35

I want to represent the fact that macro entry with id 1 has other macro companies (which are their customers) described within the same table.

Which is the best way to represent this?

Thanks!

[**Is storing a delimited list in a database column really that bad?**](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — 1000111, Jun 26 '16 at 11:43
You'd have a separate table macro_customers, each row of which would comprise of the id of the macro, and the id of its customer — Strawberry, Jun 26 '16 at 11:45
Well not the way shown. Shoot for association table or junction tables http://stackoverflow.com/a/32620163 — Drew, Jun 26 '16 at 12:28
I solved it with @Drew link, I created a separate table that links the id in the Macro table with other ids in the same table. Does it sound right? — The Condor, Jun 27 '16 at 10:07
If you have two main entity type tables (like student and course analogy) but with your tables, totally normailzed with no CSV data in columns, and a third table as a junction table similar to what I did (with decent composite indexes) .... then I think you are well on your way. Congrats ! — Drew, Jun 27 '16 at 12:52

score 0 · Answer 1 · answered Jun 26 '16 at 16:01

It depends: We all used to use the normalization forms (as @1000111 indicated), however depending on the use of the data, you can choose to look different at certain parts of this normalization discussion:

The normal model for this would be:

 Table userData(id,name) 
    - 1:N table linkTable(id,macro_customer_id) 
         - N:1 table metaData(macro_customer_id,value)

Or:

 Table userData(id,name) 
    - 1:N table linkTable(macro_customer_id,id)

The big question is however in how the data is used. If data is just for this user and not queried in any other way (no where, or group by), then storing it as a serialized String is a completely valid approach.

score 0 · Answer 2 · answered Jun 26 '16 at 16:20

The relationship between entities in an RDBMS should be stored in a relational way. Ask yourself if you care about this relationship in your database - will you need to write queries that will link macro.id to table/rows represented by IDs in macro.macro_customer_id? If yes, then you must store this relationship in a (one-to-many or many-to-many) separate table.

Supplier-customer relationship in the same table

2 Answers2