Storing an object whose attribute fields has multiple values

Question

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.

Branko Dimitrijevic · Accepted Answer · 2012-02-07 16:44:56Z

2

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.

answered Feb 7, 2012 at 16:44

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

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

Comments

Oded · Accepted Answer · 2012-02-04 22:45:35Z

0

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.

answered Feb 4, 2012 at 22:45

Oded

501k102 gold badges900 silver badges1k bronze badges

Collectives™ on Stack Overflow

Storing an object whose attribute fields has multiple values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related