Database design

[anirban1087]

Hi, I have two problems regarding my database design

1. I have a table contains the details of items, available in a store. Now there are different size available for each item. How to design my table so that it will hold each item and all available size in a single record?

My present table structure looks like -

product_code, product_name, product_brand, qty_in_hand, reorder_lev

Now say a shirt of "xyz" brand will look like

xyz11, Shirt, xyz, 150, 100

But each shirt will have different size say S, M, L, XL, XXL

How should I deal with this?

2. I also have a table called bills, where the details of each bill is saved. Now there may be a single item in a bill, and may be n no. of items in a bill. The n is very much dynamic. How to design my table to store this kind of data???

[smantscheff]

1. Add a field "size" to the product table and add a lookup table "sizes" for this field.
2. The standard layout is one table for the invoice and one table for the invoice positions (items) which are linked to the invoice table.

[reygcalantaol]

(1) Input each product with unique product_code for each size. Like for example: T-001 for Large Shirt, T-002 for Medium Shirt, in that way you can easily keep track your inventory.
(2) It is good idea if you will make three tables (a) items/products (b) bills (c) Item_bill , where items/products has (1 to n) relationship with Item_bill, and bill has (1 to n) relationship with Item_bill.

[anirban1087]

Hi,smantscheff
Thanks for the quick response. But can you please explain the second answer in details. I cant get it.

[anirban1087]

The lookup table is also not very clear to me. How to implement them? Can you give some example?

[anirban1087]

I would like to no the no. of fields per record for a bill entry? As a database table is not a "linked-list", where I can add nodes as per my requirment

[smantscheff]

I suggest you do some basic research of relational database design and learn the notions of 1:n and m:n relations.
Download an open source shop system (like OSCommerce or the like) and study their database setup.
A lookup table is a table with key-value pairs. In your item table you have a field which contains the key, in the lookup table you have some text string associated with it. You could also use the mysql enum type for that purpose, but that's not so easy to maintain as a separate table.
With proper database design you find the number of items per invoice with a query which selects all item positions for a given invoice number from the invoice details table.
For further help, show us the CREATE TABLE statements or some ERM or other design code which you have done already.

[anirban1087]

Hi,
Thanks. I have solved both the problems.