Database design variable amount of fields

Question

I'm making a website for a client but I stumbled upon a problem and I need some advice on it.

For each project, they want to have the possibility to set a variable amount of images and (sometimes) some corresponding text.

I was thinking about storing all of the information in one field, instead of making field_1 to field_99 just in case they need 99 fields.

// database column

'../fotos/foto1.png',
'hier komt tekst',
'../fotos/foto2.png',
'', (empty text)
'../fotos/foto3.png'

This solution has some disadvantadges, there must be better manners out there to achieve this.

What's the preferred way to do this?

Others have answered the question, but since you say you're doing this for a client, I just want to be sure you understand how bad an idea concatenating multiple data items or storing numerical data into a text field is. You will regret doing it. If you've already done it in other parts of your application, the time to fix it is right now, before it becomes even more difficult to do so. Text fields should only store text. — wadesworld
– wadesworld, Commented Jun 4, 2012 at 17:07

Romil Kumar Jain · Accepted Answer · 2012-06-04 12:38:46Z

3

Create another table (e.g. FOTO_CODES) with all possibly values of foto and generate id for them.

Create another child table that will have the master table record id and ID from FOTO_CODES table and FOTO data (Image).

It's called normalization.

edited Jun 4, 2012 at 12:38

answered Jun 4, 2012 at 12:33

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

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

1 Comment

Preet Sangha Over a year ago

+1 When using a relational databases, always try and turn many columns into many rows.

Branko Dimitrijevic · Accepted Answer · 2012-06-04 16:24:13Z

0

The solution you described violates the principle of atomicity and therefore the 1NF. You'd have trouble maintaining and querying data in this format.

This is a classic 1-to-many relationship, that can be modeled in two ways:

1) Identifying relationship:

enter image description here

2) Non-identifying relationship:

enter image description here

Both have pros and cons, StackOverflow already has plenty of discussions on this topic.

answered Jun 4, 2012 at 16:24

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

Collectives™ on Stack Overflow

Database design variable amount of fields

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related