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?
