CODEWITHSUNDEEP
mysqlsqldatabase
Clox
Clox

Reputation: 1943

Database column(s) that relate to one of multiple tables

I feel this is a very basic question, still I cant figure out a good way of doing it even though I've worked quite a bit with databases. Also can't seem to find anything when googling for it.

Let's say I have a bunch of tables, each describing a type of item.

For instance:

Table candles:
id█  name  █length█diameter▌burntime▌
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▌
1 █ candle1█54    █  6     █  180   ▌
69█ candle2█29    █  16    █  145   ▌
80█ candle3█100   █ 40     █  110   ▌


Table coasters:
id█  name  █thickness█diameter▌color   ▌
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀▀▀▀▀▌
1 █coaster1█5        █  20    █#ffffff ▌
6 █coaster2█8        █  41    █#ff0051 ▌
8 █coaster3█7        █ 38     █#002199 ▌


Table baskets:
id█  name  █size█
▀▀█▀▀▀▀▀▀▀▀█▀▀▀▀█
1 █basket1 █5   █
2 █basket2 █8   █
3 █basket3 █22  █

Now I have another table. Let's call it shoppingcarts. Each record in this table needs to hold an array of these items. This is where I don't know how to proceed. So I need a many to many relationship, which means I need a junction/pivot-table. But how?

Should I have one junction-table for each type of item? With a foreign key for cart-id and one for the record-id for candle/coaster/etc. Then when listing the content of the cart I need to check every single junction-table, one for each type of item. Doesn't sound very clean to me.

Or should I only have one single junction-table, which has a column for table-name and one for record-id? Seems very dirty too.

I suppose there's a much better way of doing this, but I can't think of it. How should this be done?

Upvotes: 0

Views: 532

Answers (1)

dash
dash

Reputation: 91482

One potential way of tackling this problem is to think about the items you have as objects, then think about how you might model these relationally. If you treat all of your items as objects, with properties, that have values, then this is a common schema for storing this information.

This is often referred to as O/R Mapping

A simple relational example might look like:

Object - defines all of your items

+----+----------+---------+
| Id |   Name   |  Type   |
+----+----------+---------+
|  1 | Candle1  | Candle  |
|  2 | Coaster1 | Coaster |
|  3 | Basket1  | Basket  |
+----+----------+---------+

ObjectProperty - defines all of your properties

+----+-----------+----------+
| Id |   Name    | DataType |
+----+-----------+----------+
|  1 | Length    | Float    |
|  2 | Diameter  | Float    |
|  3 | Burntime  | Float    |
|  4 | Thickness | Float    |
|  5 | Color     | String   |
|  6 | Size      | Int      |
+----+-----------+----------+

ObjectPropertyValue - defines the relationships between objects and properties, and the values 

+----+----------+------------+----------+------------+-----------+-------------+
| Id | ObjectId | PropertyId | ValueInt | ValueFloat | ValueDate | ValueString |
+----+----------+------------+----------+------------+-----------+-------------+
|  1 |        1 |          1 | NULL     | 54.0       | NULL      | NULL        |
|  2 |        1 |          2 | NULL     | 29.0       | NULL      | NULL        |
|  3 |        3 |          6 | 5        | NULL       | NULL      | NULL        |
+----+----------+------------+----------+------------+-----------+-------------+

To retrieve all of the values for a single object, you can run a query like:

SELECT          OP.Name,
                OP.DataType,
                OPV.ValueInt,
                OPV.ValueFloat,
                OPV.ValueDate,
                OPV.ValueString

FROM            ObjectProperty OP
INNER JOIN      ObjectPropertyValue OPV ON OP.Id = OPV.PropertyId

WHERE           OPV.ObjectId = 1

There are several advantages to storing information in this format:

  • It's extensible so you don't need a new table every time a new object type comes along
  • You can have as many properties as you need per object
  • Typing the property values means you can still do relevant queries based on data types

i.e. To search for all candles with a length less than 50:

SELECT      O.Id, 
            O.Name,
            OP.Name,
            OPV.ValueFloat 
FROM        Object O
INNER JOIN  ObjectPropertyValue OPV ON O.Id = OPV.ObjectId
INNER JOIN  OpjectProperty OP ON OPV.PropertyId = OP.Id

WHERE       OP.Name = 'Length'
AND         O.Type = 'Candle'
AND         OPV.ValueFloat <= 50.0;

However, there are also disadvantages:

  • It's hard to build queries on the fly without dynamic sql because you first need to know what kind of properties you are looking for on particular objects, and then choose the appropriate columns to search on in ObjectPropertyValue
  • Squeezing objects into generic structures can sometimes make very simple operations quite complex. Compare how easy it would be to get the candles in your table structure vs the query above for example.

There are other disadvantages too, but a good place to start reading about this subject in detail is O/R Mapping and Relational Data

Finally, an alternative solution for solving this problem is the concept of NoSQL, an alternative mechanism to storing objects relationally.

Jeff Atwood also has a great article on this very subject.

Upvotes: 1

Related Questions