which is better, using multiple rows, tables or arrays in mysql

Question

I am planning to store sitemaps with related data per url

wanted to know which is better :

creating new table per user / website
for each website, save all urls (with its data or in separate column) as an array in 1 row
save each url as separate row with the main website as identifier

Explained:

each user will have his own table, with all urls in their own rows
table will have columns: id, user, website, urls

for all urls of same site, it will save them as an array next to that particular website and user

same columns, but each sitemap will create many rows with 1 url per row

Paul · Accepted Answer · 2011-11-14 03:38:34Z

2

A good Design would look like this:

Table 1 (User -> Websites one to many relation):

User (PK) |  Website (FK)

Table 2 (Website -> URLs one to many relation):

Website (PK) | URL

PK = Primary Key
FK = Foreign Key

I think this is similar to your option 3 except or the separation into two tables to not store redundantly which user a website belongs to in every row.

You also may want to make a Websites table that just stores associates a website to an id:

Website Id (PK) | URL

Where you store the main URL of the website in this table, like "stackoverflow.com and then store relative urls in the (Website -> URL) relation table like "questions/8116983/which-is-better-using-multiple-rows-tables-or-arrays-in-mysql.

Then you would use Website Id's in Table 1 and 2 as well instead of Websites.

answered Nov 14, 2011 at 3:38

Paul

142k28 gold badges285 silver badges272 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nabeel Khan Over a year ago

thanks for your response, yes I will be adding your suggestions to it wanted to rule out which way is better and faster, seperate tables, arrays or normal row, looks like having rows is fine, by reducing the content in each row by removing the start of url as you said, in separate table

Paul Over a year ago

If you want to learn more about good database design you may want to lookup database normalization. BCNF may be a good place to start.

Milo LaMar · Accepted Answer · 2011-11-14 03:40:08Z

0

You do not want to create a new table for each user. Put it all in one table.

answered Nov 14, 2011 at 3:40

Milo LaMar

2,2762 gold badges15 silver badges25 bronze badges

Collectives™ on Stack Overflow

which is better, using multiple rows, tables or arrays in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related