CODEWITHSUNDEEP
databasedatabase-design
user1162293
user1162293

Reputation: 11

Storing an object whose attribute fields has multiple values

I have a data storage requirement where each item has several fields and each field may contain more than one value (strings). And I need to run a Search query where item(s) will be selected based on one or more of the field values. here is structure what I need :

Name:     (this will be the name of an item)
Season:   (name of the season)
Taste:    (1 taste or several)
Funktion: (1 or several option)
Weight:   (1 or several)
Volume:   (1 volume or several)
Tips:     (for comments)
Flavor combinations: (1 or several)
Matching flavors: (several)

Please any suggestions regarding both the database design search queries for MySql.

Upvotes: 1

Views: 262

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

A simple "one-to-many" should work:

enter image description here

You search by JOINing ITEM with each of the "child" tables.

If you wish to limit the attribute values, you can do a "real" many-to-many:

enter image description here

So, an item cannot be associated with a taste that is not already in the TASTE table (etc. for other kinds of attributes).

Or you could even make a generalized model, so you are not limited to any particular set of attributes:

enter image description here

Caveat: this is very flexible and you now need to JOIN with just one table, but consistency is harder to enforce and may be necessary to do at the application level.

Upvotes: 2

Oded
Oded

Reputation: 498904

The standard design for multi valued attributes is a many-to-many table - this would normally consist of a compound primary key consisting of two foreign keys - one for each of the join tables.

The query on these would involve this many-to-many table.

Upvotes: 0

Related Questions