Help with clothing order management database

[jsrdixon]

Hi,

I am trying to design and build an orders management database for a clothing wholesaler.

Has anyone got any ideas on what entities i should have.

I thought:

Clients (Customer Details)
Orders (Inc. order date, ship via etc)
Order Details (sizes, quantity of orders per size)
Product Details (Product Code, colour, size, stock numbers per size)
Catergories (Shorts, Jackets, Jeans etc)
Suppliers (Supplier Information)
Delivery (Delivery Method etc)

However, loads of problems when i come to planning the sizes part. I have different product catergories with different sizes

And when i do the order details table, i need to enter order quantities for each different size for one clothing unit.

Any ideas?

I would also ideally want to link the order details and products tables by the product code number. I.e. When filling out an order In the order details table i want to type in the product code then have information from the product table automatcially fill in the gaps in the order details table, such as unit price, colour etc.

Hope you understand and can help me!!!

Thanks for your time
J

 

[neileg]

I would treat each size as a separate item. The combination of the product code and the size should give you a unique identifier.This way you still deal with the product as a whole but keep the different size details separate.

To enter product details, I think I would use a combo box to select the product code, and use this to drive a second combo to pick the size. Search 'cascading combo' in these forums.

Don't store the stock balance in your product table. You should calculate the balance from a series of transactions in and out of stock. Search in these forums for inventory or stock control.

 

[jsrdixon]

Thats helpful, thank you.

Just a thought though. There are only 5 different size scales in total, i.e. outawear, jeans, shirts, shorts, tees.

Should i create a seperate table with these size scales then link cascading ocmbo boxes.

I could reference them A-E, then when a user selects a product code, it pulls up the size reference then a combo box links to that size reference only offering the sizes within that range.

Does this sound right, or more complex than i need. Im a beginner so need it simlpified at this stage.

THanks

 

[neileg]

Sounds good to me. Preloading these pick lists cuts down on user error or inconsistency.

 

[jsrdixon]

ok,

you mentioned its best to mdel each product size individually. However, with our products, it would mean having to repeat a product description, colour code, colour description etc etc approx. 5x (Small, Medium, Large, XL, XXL)

Surely there is a way to have one row with all the product details on, and be able to enter the quantities ordered for each size in the same row. Then at the end have a total quantity.

How do i model this. Do i store Size1, Size2, Size3, Size4, Size5 in the order details table or is there a better way.

If i do the above - doesnt that mean i have to do the same in the products table to ensure stock calcluations can be syncronised.

PLEASE HELP!!!!

thanks

 

[neileg]

You are thinking in spreadsheet mode. In a well designed relational database, the tables will often be much more complex than you would expect if you used a spreadsheet. This 'long winded' approach gives the designer maximum flexibility and easy of use. Remember that it's Access that deals with five record for five different sizes, not the user. Users don't see tables, they see forms and reports. These should look nice and neat.

Suppose you want to know how many Levi 501s a customer bought. All you do is sum the transactions ignoring the size. If you have fields showing Size1, Size 2, etc, then you have to sum those per transaction and then sum the sums. Supposing you want to know how many garments regardless of category. If each category has a different range of sizes, you may have to use different queries for each category, then combine the queries. How about when you need to add a new category, or when outerwear gains an extra size? You're into a major redesign of the database, instead of simply adding one new entry in a table.

This approach is known as normalisation, and it's where most spreadsheet users come unstuck in their learning curve in Access. I know I did! Do a search in these forums and in Google for more advice.

 

[jsrdixon]

So you think i should do it by literally having a list of products hundreds long.

i.e.

[ProductUniqueID] [ModelNo] [Catergory] [Description] [Colour] [Size]

[1] [0161] [Polo] [Classic Polo] [Black] [Small]
[2] [0161] [Polo] [Classic Polo] [Black] [Medium]
[3] [0161] [Polo] [Classic Polo] [Black] [Large]
[4] [0161] [Polo] [Classic Polo] [Red] [Small]
[5] [0161] [Polo] [Classic Polo] [Red] [Medium]
[6] [0161] [Polo] [Classic Polo] [Red] [Large]
[7] [0214] [Shorts] [Bermuda Classic] [Khaki Green] [32]
[8] [0214] [Shorts] [Bermuda Classic] [Black] [34]
[9] [0214] [Shorts] [Bermuda Classic] [White] [36]
[10] [0354] [Jeans] [Army SS] [Stonewash] [33/34]
[10] [0354] [Jeans] [Army SS] [Stonewash] [34/36]

etc etc etc etc

If so, then do i organise products into catergories so when im selecting a product for an order i choose a catergory then from there select a product. is this the best way?

 

[neileg]

So you think i should do it by literally having a list of products hundreds long.

i.e.

[ProductUniqueID] [ModelNo] [Catergory] [Description] [Colour] [Size]

[1] [0161] [Polo] [Classic Polo] [Black] [Small]
[2] [0161] [Polo] [Classic Polo] [Black] [Medium]
[3] [0161] [Polo] [Classic Polo] [Black] [Large]
[4] [0161] [Polo] [Classic Polo] [Red] [Small]
[5] [0161] [Polo] [Classic Polo] [Red] [Medium]
[6] [0161] [Polo] [Classic Polo] [Red] [Large]
[7] [0214] [Shorts] [Bermuda Classic] [Khaki Green] [32]
[8] [0214] [Shorts] [Bermuda Classic] [Black] [34]
[9] [0214] [Shorts] [Bermuda Classic] [White] [36]
[10] [0354] [Jeans] [Army SS] [Stonewash] [33/34]
[10] [0354] [Jeans] [Army SS] [Stonewash] [34/36]

etc etc etc etc

Perhaps. Alternatively, you could have a table for products, and a subtable for sizes and even a subtable for colours. It all depnds what your business model dictates.

If so, then do i organise products into catergories so when im selecting a product for an order i choose a catergory then from there select a product. is this the best way?

You're overlapping the table design with the user interface. If you have experienced users, you will find that they get to learn some or all of the product codes. It may prove irritating and a clog on productivity to force them to go through a series of pick lists when they could just type 'XYZ123'. That kind of choice will not be limited by proper table design and you may even offer more than one user interface depending on the expertise or experience of the user.

 

[jsrdixon]

With regards to the sub tables. I have another post running with a similar question.

e.g. colour sub table:
There are about 10 unique colours for each model and there are possibly over 100 different models.

If i have a subtable for colours, how will i ensure that for one model i can only select the specific colours for that model.

This goes the same for sizes as each catergory has different size ranges.

thanks for your help, sorry for all the questions

 

[neileg]

If you have a subtable for colours then you have a many to many relationship between models and colours. You will need to use a junction table to join your two tables. This will hold, as a minimum, the PK from the model table and the PK from the colour table. The your user picks from the junction table since this will hold only those combinations that are valid.