Jump to content

Recommended Posts

I'm sorry for the previous post. I pressed the post button instead of preview while I'm typing the description. So here's the completed post.

 

I'm having difficulty in designing my database for a multi-level distribution system. A distributor receives stocks from a supplier. A distributor has one or many dealers. A dealer has one or many retailers. For example the supplier issues a batch of product X to the supplier. Let's call it Product X Batch One (1), with serial numbers xxxx1 - x9999, expiring on YYYY-MM-DD. Then the distributor issued the batch one s/n xxxx1 - xx999 to dealer "A", s/n x1000 - x1999 to dealer "B" and s/n x2000 - x2999 to dealer "C". Then the dealers will distribute them to their retailers. My database design for my inventory is as follows:

CREATE TABLE stocks (
   stock_id int(10) NOT NULL AUTO_INCREMENT,
   product_id int(3) NOT NULL,
   batch_id int(6) NOT NULL,
   expire date NOT NULL,
   quantity int(6) NOT NULL,
   capital decimal(10,2) NOT NULL,
   PRIMARY KEY (stock_id)
)

 

What if the distributor wants to trace which serial numbers are still in the inventory and to which dealers are the missing numbers have been issued? How should I design my database? Here's what I have in mind so far:

 

CREATE TABLE serials (
   sn_id int(10) NOT NULL AUTO_INCREMENT,
   stock_id int(10) NOT NULL,
   sn varchar(6) NOT NULL,
   taken int(1),
   PRIMARY KEY (sn_id)
)

 

Please feel free to criticize my table. If you have better suggestions, I would be glad to implement it. Thanks so much guys!

 

Here are my other tables involved in the inventory movements:


CREATE TABLE purchases (
  purchase_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  supplier_id int(4) unsigned NOT NULL,
  delivery date NOT NULL DEFAULT '0000-00-00',
  line_total decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (purchase_id)
)
CREATE TABLE purchase_details (
  pd_id int(15) unsigned NOT NULL AUTO_INCREMENT,
  purchase_id int(10) unsigned NOT NULL,
  product_id int(5) unsigned NOT NULL,
  quantity int(6) unsigned NOT NULL,
  expire date NOT NULL,
  unit_price decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`pd_id`)
)
CREATE TABLE sales (
  sale_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  dealer_id int(6) unsigned NOT NULL DEFAULT '0',
  due_date date NOT NULL,
  line_total decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (sale_id)
)
CREATE TABLE sale_details (
  sd_id int(15) unsigned NOT NULL AUTO_INCREMENT,
  sale_id int(10) unsigned NOT NULL,
  product_id int(5) unsigned NOT NULL,
  capital decimal(10,2) NOT NULL,
  quantity int(6) unsigned NOT NULL,
  sell_price decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (sd_id)
) 

Link to comment
https://forums.phpfreaks.com/topic/259330-db-design-problem/
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.