I believe the title is self-explanatory. How do you create the table structure in PostgreSQL to make a many-to-many relationship.
My example:
Product(name, price);
Bill(name, date, Products);
databasedatabase-designmany-to-manypostgresqlsql
I believe the title is self-explanatory. How do you create the table structure in PostgreSQL to make a many-to-many relationship.
My example:
Product(name, price);
Bill(name, date, Products);
Best Answer
The SQL DDL (data definition language) statements could look like this:
I made a few adjustments:
The n:m relationship is normally implemented by a separate table -
bill_product
in this case.I added
serial
columns as surrogate primary keys. In Postgres 10 or later consider anIDENTITY
column instead. See:I highly recommend that, because the name of a product is hardly unique (not a good "natural key"). Also, enforcing uniqueness and referencing the column in foreign keys is typically cheaper with a 4-byte
integer
(or even an 8-bytebigint
) than with a string stored astext
orvarchar
.Don't use names of basic data types like
date
as identifiers. While this is possible, it is bad style and leads to confusing errors and error messages. Use legal, lower case, unquoted identifiers. Never use reserved words and avoid double-quoted mixed case identifiers if you can."name" is not a good name. I renamed the column of the table
product
to beproduct
(orproduct_name
or similar). That is a better naming convention. Otherwise, when you join a couple of tables in a query - which you do a lot in a relational database - you end up with multiple columns named "name" and have to use column aliases to sort out the mess. That's not helpful. Another widespread anti-pattern would be just "id" as column name.I am not sure what the name of a
bill
would be.bill_id
will probably suffice in this case.price
is of data typenumeric
to store fractional numbers precisely as entered (arbitrary precision type instead of floating point type). If you deal with whole numbers exclusively, make thatinteger
. For example, you could save prices as Cents.The
amount
("Products"
in your question) goes into the linking tablebill_product
and is of typenumeric
as well. Again,integer
if you deal with whole numbers exclusively.You see the foreign keys in
bill_product
? I created both to cascade changes:ON UPDATE CASCADE
. If aproduct_id
orbill_id
should change, the change is cascaded to all depending entries inbill_product
and nothing breaks. Those are just references without significance of their own.I also used
ON DELETE CASCADE
forbill_id
: If a bill gets deleted, its details die with it.Not so for products: You don't want to delete a product that's used in a bill. Postgres will throw an error if you attempt this. You would add another column to
product
to mark obsolete rows ("soft-delete") instead.All columns in this basic example end up to be
NOT NULL
, soNULL
values are not allowed. (Yes, all columns - primary key columns are definedUNIQUE NOT NULL
automatically.) That's becauseNULL
values wouldn't make sense in any of the columns. It makes a beginner's life easier. But you won't get away so easily, you need to understandNULL
handling anyway. Additional columns might allowNULL
values, functions and joins can introduceNULL
values in queries etc.Read the chapter on
CREATE TABLE
in the manual.Primary keys are implemented with a unique index on the key columns, that makes queries with conditions on the PK column(s) fast. However, the sequence of key columns is relevant in multicolumn keys. Since the PK on
bill_product
is on(bill_id, product_id)
in my example, you may want to add another index on justproduct_id
or(product_id, bill_id)
if you have queries looking for a givenproduct_id
and nobill_id
. See:Read the chapter on indexes in the manual.