question: n:m association between three tables

From: Adam Šindelář <adam(dot)sindelar(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: question: n:m association between three tables
Date: 2008-01-10 20:16:10
Message-ID: 1a3f89540801101216l85364fal1bd5746a9e9173b3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message LWATCDR 2008-01-10 20:24:23 Re: moving a database.
Previous Message Frank Bax 2008-01-10 16:56:29 Re: moving a database.