Right now performance isn't a problem, but this question has me curious:
Let's say I have a shopping cart system where there is a "products"
table that contains all possible products, and an "cart_items" table
that stores how many of each product are in each cart.
The obvious (or the first thing that came to my mind) would look
something like this:
create table products (
id serial primary key,
...
);
create table cart_items (
id serial primary key,
cart_id int references ...,
prod_id int references product(id),
quantity int
);
The problem is, when you add the first item to "cart_items" you have to
do an INSERT with a quantity of 1, but after that you need to do
UPDATEs. That would seem to create a potential race condition, so in
order for that to work it would seem you would need to do an ACCESS
EXCLUSIVE lock on the table to make sure no other process was reading
the table at the same time.
Assuming my logic above is correct, there are two other ways I thought
to do it, but both seem considerably more redundant:
(1) I could just get rid of the "quantity" attribute and just insert a
record for each product, then do a view that aggregates the products of
the same prod_id and cart_id with count().
(2) Every time I add a product I could add a record with a quantity of 0
for each cart in existance, and every time I add a cart I could add a
record with a quantity of 0 for each product.
Is there some better solution that I'm missing? It seems like a simple
problem, but right now I'm doing the full table lock to be on the safe
side. Maybe there's some solution involving check constraints?
Regards,
Jeff Davis