Re: question: n:m association between three tables

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.