CODEWITHSUNDEEP
mysqlarrays
Nabeel Khan
Nabeel Khan

Reputation: 3993

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

I am planning to store sitemaps with related data per url

wanted to know which is better :

  1. creating new table per user / website

  2. for each website, save all urls (with its data or in separate column) as an array in 1 row

  3. save each url as separate row with the main website as identifier

Explained:

  1. each user will have his own table, with all urls in their own rows

  2. 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

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

Upvotes: 1

Views: 256

Answers (2)

Milo LaMar
Milo LaMar

Reputation: 2256

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

Upvotes: 0

Paul
Paul

Reputation: 141829

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.

Upvotes: 2

Related Questions