How Would You Structure This?

[eluser]mdavis1982[/eluser]
Hi all...

I've been tasked with writing an online store, but with a difference. The client wants to queue up a series of products and give them all an initial stock value. The site needs to display only one product at a time, and when that product sells out, they want the next product to appear automatically.

They also want the ability to alter the order of the product queue at any time, and be able to restock products and add them back into the queue wherever they want.

How would you structure this both in terms of database design, and models/controllers/views?

Any help would be greatly appreciated!

Thanks...

Matt

[eluser]wiredesignz[/eluser]
Are you being paid to write the application?

[eluser]mdavis1982[/eluser]
Yes... But I'm not sure why that matters.

I have a very good idea how I want to structure it all, but I'm looking for other people's suggestions.

[eluser]xwero[/eluser]
It seems like a fairly easy thing to do.

For the frontend you would have one page for this where the first product in the list is shown with the possibility to buy it. For the backend you would have a page where they see the list of products which they can alter and a part where they can add products to the list from their product list. Then you would have a page where the can add and delete products.

For the database i would say at least 3 tables : products, productlist, orders. But it depends on which data they require. If they want to keep previous stocks, if they would have centralized customer data, ...

[eluser]mdavis1982[/eluser]
Thanks for your input, xwero.

I'm not really interested in how to structure the back end as that's fairly straightforward; I'm more concerned with getting the DB schema set up properly and how to make sure that I only enable the sale of the requisite number of products so that they don't go over their stock allowance!

Thanks..

Matt

[eluser]xwero[/eluser]
I fail to see the problem there? In the table productlist table there is the current stock. And you substract it with the number of the items once an order is submitted. If you allow multiple items i think you best go for a select list or a spinbutton that only goes to the highest stock possible and you check the current stock before you confirm the order.

[eluser]BlueCamel[/eluser]
Well, I would do this. It's like a queue

Code:

$items = array(
   "Item 1: $50.00",
   "Item 2: $75.00",
   "Item 3: $100.00",
);

while ($items) {
   print "<p>Now for sale!!! - ". array_shift($items) "</p>";
   sleep(500);
};

[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?

[eluser]mdavis1982[/eluser]
Hi Thurting...

Thank you for your suggestions... I'm always looking to outsource work because, to be honest, I've got too much to do!

Can you send me a PM or E-Mail so we can have a chat about it?

Thanks...

Matt

[eluser]obiron2[/eluser]
I'd have a table

Table:Queue

QueueID (autonumber)
QueueName

Table:QueueList

QueueID
QueuePosition (int)
ProductID (varchar)
StockLimit (int)

Primary: QueueID/QueuePosition
Unique: QueueID/ProductID

This way you can put a product into more than one queue and set different expiry limits on it.

To build your display product list, I would join to the Product table on ProductID and select where Product.AvailableStock >= StockLimit order by QueueList.QueuePosition

I would have thought your bigger problem would be the admin side. Moving database records up and down a manual preference is normally a pretty torrid affair unless you are going to use Ajax/javascript functionality to make it drag and drop.

obiron