How Would You Structure This?

[eluser]thurting[/eluser]
How about this as a simple schema:

Code:

create table products
(
  id int unsigned not null auto_increment,
  name varchar(255) not null,
  price decimal(5,2) unsigned not null default 000.00,
  inventory int unsigned not null default 0,
  active int unsigned not null default 0,
  primary key (id),
  unique (name)
) engine=InnoDb;

The important piece here is the "active" column. Let's assume this is a boolean and that if a product should be displayed it will be active (active = 1). Now, when you display the page, you simply select the product that is active (where active = 1). When a transaction completes, you subtract the relevant value from the inventory column. If the inventory > 0, you are cool. If inventory = 0 (I assume you check if a user tries to buy more items than in current inventory during the checkout process), then you simply deactivate the current active product (where active = 1, active = 0) and activate the next product in line (where id = current id + 1, active = 1). You may want to create a separate table that holds the order that products should be displayed and work from this to move from one product to another. There are many ways to do it.

You said you understand how to build the back end, so I wont get into that. What I have listed above is just an example, but should help you get started. I think using InnoDb with transactions is probably best considering your requirements.

Looking to contract any work out?