Connecting Two Items in a Database - Best method?

Question

In a MySQL Database, I have two tables: Users and Items

The idea is that Users can create as many Items as they want, each with unique IDs, and they will be connected so that I can display all of the Items from a particular user.

Which is the better method in terms of performance and clarity? Is there even a real difference?

Each User will contain a column with a list of Item IDs, and the query will retrieve all matching Item rows.
Each Item will contain a column with the User's ID that created it, and the query will call for all Items with a specific User ID.

2nd approach is better because it defines one-to-many relationship and it also defines that you have to create two tables (user & item) and uou can easily join both tables and index also be used for that query — Saharsh Shah, Dec 25 '13 at 06:28
'KISS' (Keep It Simple,Stupid) it. This is a typical one-to-many relationship, 2nd approach will solve it. — Ahamed Mustafa M, Dec 25 '13 at 06:31
See also [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/3653574#3653574) — Bill Karwin, Dec 25 '13 at 06:35

score 2 · Answer 1 · edited Dec 26 '13 at 23:19

2

Let me just clarify why approach 2 is superior...

The approach 1 means you'd be packing several distinct pieces of information within the same database field. That violates the principle of atomicity and therefore the 1NF. As a consequence:

Indexing won't work (bad for performance).
FOREIGN KEYs and type safety won't work (bad for data integrity).

Indeed, the approach 2 is the standard way for representing such "one to many" relationship.

edited Dec 26 '13 at 23:19

Basil Bourque

303,325
100
852
1,154

answered Dec 25 '13 at 14:05

Branko Dimitrijevic

50,809
10
93
167

@BasilBourque You have linked "1NF" and "FOREIGN KEYs" to some YouTube video about Jesus Christ. What's up with that?? – Branko Dimitrijevic Dec 26 '13 at 21:55
Sorry about my incorrect links. That will be the last time I try to do an edit on an iPhone. I have now installed correct links to Wikipedia articles for background info. – Basil Bourque Dec 26 '13 at 23:20

score 1 · Answer 2 · answered Dec 25 '13 at 06:33

1

As long as an item doesn't have multiple owners it's a one to many relationship. This typically gets reduced to the second approach you mention, eg. have a user or created_by column in the Items table.

answered Dec 25 '13 at 06:33

rath

3,655
1
40
53

score 1 · Accepted Answer · answered Dec 25 '13 at 06:33

2nd approach is better, because it defines one-to-many relationship on USER to ITEM table.

You can create foreign key on ITEM table on USERID columns which refers to USERID column in USER table.

You can easily join both tables and index also be used for that query.

Basil Bourque · Answer 4 · 2013-12-26T23:24:31.833

If a User can have one or more Items but each Item is owned by only a single User, then you have a classic One-To-Many relationship.

The first option, cramming a list of related IDs into a single field, is exactly the wrong way to do it.

Assign a unique identifier field to each table (called the primary key). And add an extra field to the Item table, a foreign key, the id of the User that owns that item.

Like this ERD (entity-relationship diagram)…

Diagram of two tables, parent table user_ and child table item_

You have some learning to do about relational database design and normalization.

Connecting Two Items in a Database - Best method?

4 Answers4