Best structure for this database?

Question

The Problem: I want to design a website and I need a database for that, however I don't know which structure is better!

What will happen: Users will add some URIs to their favorites.

Possible structures:

Structure one:

TABLE "USERS":
=====================================================================
id | name | last_name | urls
1  | John | Smith     | [google.com,stackoverflow.com,yahoo.com,...]
=====================================================================

Structure two:

TABLE "USERS":
=============================================
id | name | last_name 
1  | John | Smith
2  | Joe  | Roth
==============================================

TABLE "URLS":
==============================================
id | user_id | url
1  | 1       | google.com
2  | 1       | stackoverflow.com
3  | 2       | ask.com
4  | 1       | yahoo.com
5  | 2       | being.com
==============================================

Which structure is best? Thanks in advance!

Your second designs looks good to me. Steer clear of the first one... — Abe Miessler, Jan 02 '14 at 16:15
Your first "database organization" violates even the 1NF - the second is definitely better. — Submersed, Jan 02 '14 at 16:18
See [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, Jan 02 '14 at 21:04

score 1 · Answer 1 · answered Jan 02 '14 at 16:16

1

Absolutely create another table that save urls + user_id. Your first type is not in Normal form.

answered Jan 02 '14 at 16:16

Casero

322
2
12

1

Specifically, it's not in _first_ normal form. – Branko Dimitrijevic Jan 03 '14 at 03:13

score 1 · Accepted Answer · answered Jan 02 '14 at 16:23

The second schema (with a foreign key constraint on URLS.user_id) will be easier to manage. A select query will need to use a join and you'll be performing more inserts, but you won't need to perform string parsing to figure out what the urls are (which, with the first schema, you would need for every single select and update).

One of the tables in the production database at my current job has a schema similar to the first case, and it makes my entire department cringe and complain when they have to write code working with it. Yet the table remains, because fixing it would be a massive overhaul. (The table was created by a manager who no longer works here.) If you're creating your schema now, do it right, while you still can.

Best structure for this database?

2 Answers2