CODEWITHSUNDEEP
mysqldatabase-design
sanjihan
sanjihan

Reputation: 6026

MySql table design with variable number of columns

Take a look at this scenario: a parses collects all links on a website and store it in a table. Now, there is no way of knowing in advance how many links there are on a website page, so it is impossible to prepare a table with appropriate number of columns.

-------|-------|-------|-------|---------------
website| link1 | link2 | link3 | maybe100columns

What is the common way to tackle this problem, when you don't know how many columns you need? Do you encode all the links in json format and store json in 1 column instead?

Upvotes: 1

Views: 625

Answers (2)

UkFLSUI
UkFLSUI

Reputation: 5672

Why do you need to create each column for each link? The relational database system is introduced to tackle these types of problems. A simple solution would be to use two tables for your scenario.

In one table you will keep the website column and another column with primary key should be assigned to say website_id column.

In your 2nd table there will be primary key assigned to a column say link_id, then a foreign key assigned to column website_id which will be in the reference of website_id of 1st table and finally another column named as say link.

So, by this way you will store as many link as required for any site.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

the correct way is based on normalization instead of 

-------|-------|-------|-------|---------------
website| link1 | link2 | link3 | mybe100columns

you should use a nornalized couple of table

master

------------|-------
id_website  |website

detail 

---|------------|----
id |id_website  |link

Upvotes: 3

Related Questions