DB Design - Products with Variables (eg Size/Colour)

[lcot]

Hi,

I am currently designing a new database to create an order/customer/product management system for my personal use to better organise the workflow of my company.

However, some of my products have variables such as size or colour. For example, a t-shirt comes in sizes Small, Medium, Large and XL. Another example is a jewellery item coming in silver, rose gold and gold.



My question is, to keep the invoices short and to save so much time with regular product data entry adding in each variable individually (which may encourage human errors), how would you set up your tables to account for these variables with the products and how would these be incorporated into an access order form.

I am fairly new with MS access but have enough experience to build a basic database. So far, all is going fine but I have hit a road block with this issue.

My current table list is as follows:

• Customers
• Order Categories
• Order Details
• Orders
• Order Status
• Product Categories
• Products
• Value Adding Factors (eg agency commission rate, tax rate etc)

Any help and support is welcome!
Thanks!

[orange]

With Access you will save yourself a lot of grief it you choose a naming structure that does not have embedded spaces or special characters in field and object names. Use only alphanumeric and underscore ("_").

How do you do invoicing currently? What issues?
Would SKU and scanner be cost effective?

You might consider a table of Attributes with
AttributeID
AttributeName
AttributeValue.

Then combine ProductId and AttributeID to get info for SKU??? Just typing and thinking.

You may get some more info from the links to videos on this page

[lcot]

Duly Noted about the naming structure. Thank you!

I've attached below the table structure for you and their relationships... Any good tutorials for creating an invoice style order form.

I'm currently at the stage where I am struggling to get the form to work like the following:
New Order - All fields empty
There is a customer field, to select the customer.
I can then select the products using a combo box. I select the product, the attribute, the quantity and then the unit price / total price appear. When one row has been entered, I can enter another row as it appears. Any advice?

[orange]

Before getting to the form, does your model support your requirements?
Take a print out of your model -- works best on paper.
Identify what data you need to get out of your database. Could be form, report, query...

Create some typical scenarios and some test data. Test your model with the test data and scenarios. If any issues arise, determine exactly what is the issue and cause. Then make whatever adjustments you deem necessary. Retest until the model works. Then focus on the forms.

Is it possible your want to differentiate Shipping as an entity / Do you have partial shipments/deliveries?

As for your model, a couple of minor points to consider:

Each table has ID as PK. Doesn't really tell much. You might consider CustomerID, OrderID, ProductId etc. It can prevent confusion when coding or communicating with others.
Can a Customer have multiple addresses important to you? Mailing/Billing, HQ, Shipping??
Re VAT, does Vat apply to an Order or Product?

In Product you show Category - I think you may want to have a separate entity "Category" with CategoryID, CategoryName, CategoryDescription
Then use CategoryID in the Product Table. Seems 1 category contains 1 or many Products, and 1 Product fits in 1 nd only 1 Category(but I don't know your rules)

Unit Price and Discount ?? When you sell a Product, the OrderDetails should have a type of AgreedToPrice. This isn't always the Unit or List Price, BUT you need to record the "agreedToPrice" ( or whatever you call it) and define it on a per item or something and the Quantity sold. You need this in the OrderDetails to avoid changes happening to existing Orders hen you change a Products price in the ProductTable. (This is often overlooked).
Do Discounts relate to Customer? eg Customer XX gets 10% on Sales > $300. It may have impact on your model.

Allocated, invoiced, Deposit Paid, Shipped, Completed may not be attributes of Order Details. You need to review your Business processes and rules in more detail, and confirm where these fit. Your test data should help this decision/confirmation. It sounds like you are including Invoicing and Shipping in Order Details (maybe not -- I 'm just reading your model without knowing the details of your business).