Thread: question: n:m association between three tables

Hi,

I hope this is the right place to ask about this: I'm building a simple eshop for a restaurant that should allow its users to order meals over the Internet, and also add some goodies to their order, like teapots and guitar strings, and god knows what else. Anyway, the products are stored in two tables: items, and meals. They look something like this:

CREATE TABLE meals (
id serial unique primary key,
price float,
name varchar(50),
description text);

CREATE TABLE items (
id serial unique primary key,
price float,
name varchar(50)
description text);


The actual schema is a little more complicated by the fact that the database must also store some additional data about meals, such as the ingredients so the staff know what they need to buy to cook the orders. Long story short, it's not possible to fit meals and items into a single table. The meals and items are ordered using these two tables:

CREATE TABLE orders (
id serial unique primary key,
user_id integer not null references users(id) on delete cascade);


CREATE TABLE ordered_items (
item_id integer not null references items(id) on delete cascade,
order_id integer not null references orders(id) on delete cascade);

The obvious problem with this schema is that there is no way to order meals because I can't figure out a way to somehow make ordered_items work with both meals and items. I know there's table inheritance, and I've messed around with it but it causes problems with foreign keys (records in the child table appear in the parent table but can't be referenced). I also know that Ruby on Rails uses polymorphic associations, but that not only (probably) doesn't work with foreign keys, but also seems slow and, for some reason that I can't quite put my finger on, evil. So, are there any ideas on what the proper way to do this is?

Thanks in advance!

Adam

On Jan 10, 2008 3:16 PM, Adam Šindelář <adam.sindelar@gmail.com> wrote:
> Anyway, the products are stored in two tables: items, and meals.

Hmm. Two tables that seem to have the same structure. I smell
attribute splitting. They ought to be one table with an attribute that
differentiates them. You already defined what they are (products) and
what types they can be ('item' or 'meal').

> The actual schema is a little more complicated by the fact that the database
> must also store some additional data about meals, such as the ingredients so
> the staff know what they need to buy to cook the orders. Long story short,
> it's not possible to fit meals and items into a single table.

Why? You can have a separate table to store whatever ingredients are
required for a specific product of type 'meal'. So why don't you try:

CREATE TABLE products (
  product_id SERIAL NOT NULL UNIQUE,
  product_type TEXT
    CHECK (product_type IN ('meal','item')),
    -- Or reference another table.
  product_price NUMERIC(11,2),
    -- Or whatever the appropriate precision/scale is.
    -- *DO NOT USE FLOAT*
  product_name TEXT,
  product_description TEXT,
  PRIMARY KEY (product_type, product_name)
    -- Personal choice. Natural keys make me feel
    -- all warm and fuzzy inside...
);

CREATE TABLE meal_ingredients (
  product_id INT REFERENCES products(product_id),
    -- ... but you just gotta love surrogate keys.
  ingredient_name TEXT,
    -- You could also have a separate table for
    -- ingredient names. It's up to you.
  PRIMARY KEY (product_id, ingredient_name)
);

Otherwise, you need to provide more details (DDL, etc.).

> The meals and items are ordered using these two tables:
>
> CREATE TABLE orders (
> id serial unique primary key,
> user_id integer not null references users(id) on delete cascade);
>
> CREATE TABLE ordered_items (
> item_id integer not null references items(id) on delete cascade,
> order_id integer not null references orders(id) on delete cascade);

These could become:

CREATE TABLE orders (
  order_number SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(user_id)
    -- Barring space constraints, I don't
    -- recommend that you delete any info,
    -- for future reference.
    -- But it depends on your requirements,
    -- and what you have to work with.
);

CREATE TABLE ordered_items (
  order_number INTEGER REFERENCES orders,
  product_id INT REFERENCES products(product_id),
  PRIMARY KEY (order_number, product_id)
    -- Never forget your PKs.
);

I'd recommend you include a temporal table to store product price
information. To do that right, I recommend you read "Developing
Time-Oriented Database Applications in SQL". PDF found here:

http://www.cs.arizona.edu/~rts/publications.html

Really good stuff.

You'll notice I renamed your columns. For example, 'id' is too vague
for a column name. Column names should be descriptive and, preferably,
unique in your entire schema.

Whatever design you come up with really depends on the requirements
given by your client. Sit down with them to clear things up.

Good luck.