Re: question: n:m association between three tables
От | Rodrigo E. De León Plicet |
---|---|
Тема | Re: question: n:m association between three tables |
Дата | |
Msg-id | a55915760801102350k3f6b93aald69f280c97beba05@mail.gmail.com обсуждение исходный текст |
Ответ на | question: n:m association between three tables ("Adam Šindelář" <adam.sindelar@gmail.com>) |
Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: