CODEWITHSUNDEEP
sqldatabase-design
guy1997
guy1997

Reputation: 286

Database design variable amount of fields

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?

Upvotes: 1

Views: 173

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

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.

Upvotes: 3

Related Questions