need suggestion for normalization

[erickwidya]

1. is this database design correct? (plz design it the way u think it right..)
2. 1 invoice is for one orders right? if i drag a relationship from invoice to orders..than it have 1 to many relationship from orders to invoice..how to prevent this (to make it the way i want..invoice only have one orders)?

thanks alot for the replies..

[jp140768]

Morning,

Took a look at your database, I have a question about two of your tables:
ProductDetails and OrderDetails - they both look to be doing the same thing / keeping the same data.

Will there always only ever be one supplier for a product? If not, you may need to do something around the design of the product table.

Why do you have an ordersdetails table and a customerordersdetail table? Likewise with orders and customerorders. Assuming that there cannot be an order without a customer, you could drop the orders and orderdetails table and just use the customerorders and customerorderdetails tables.

You record the quantity being ordered, so perhaps you should also have a field in the products table called items in stock - something that tells you whether we can fill this order or not.

If you can only have one invoice per order, would you not be better to move the two distinct fields (DueDate and IsPaid) into the Customer Orders table?

You may want to have more than one invoice per order though for example people who are given a period of credit, they can put through multiple orders and then at the end of the month, we produce an invoice for the orders placed last month - I guess your credit card statement is a good example of this, each line on the statement could be an order, and the statement is your invoice. If you want to go down this road, you need an extra table called InvoiceOrders. Table Invoice would have static details eg amount paid, due date etc, and invoiceorders would link the invoice to the orders that you are invoicing for.

You asked how to stop a one to many relationship between Invoice and Orders, if you change the index property of ORDERID in table Invoice to being No Duplicates, you get a 1 to 1 relationship.

HTH

[erickwidya]

morning too :0
ProductDetails and OrderDetails - they both look to be doing the same thing / keeping the same data.one product can have 1 or more Price that's why i got ProductDetails..at ProductDetails i have Price and Quantity that similar to OrderDetails..coz if the product got sold than i have to minus the quantity at ProductDetails rather than at OrderDetails..

Will there always only ever be one supplier for a product? Yes, it is..and one supplier can have many product

Why do you have an ordersdetails table and a customerordersdetail table? well the CustomerOrders and CustomerOrderDetails is to handle orders from customers..and the Order and OrderDetails is to handle my(in this case my company) orders to Supplier..i have a thought to go on same table..but it i can't figure it out how to make it different between customer orders and company orders..and i think it might be difficult to trace the orders if it come from 1 table..that's why i separate this..

You record the quantity being ordered, so perhaps you should also have a field in the products table called items in stock - something that tells you whether we can fill this order or not. this can tell from ProductDetails..is my thought right?

If you can only have one invoice per order, would you not be better to move the two distinct fields (DueDate and IsPaid) into the Customer Orders table? isn't that DueDate and isPaid depend on InvoiceID rather than OrderID?

You may want to have more than one invoice per order though for example people who are given a period of credit.. i already talk to person in charge of this..and she said that only one invoice for one order..

You asked how to stop a one to many relationship between Invoice and Orders, if you change the index property of ORDERID in table Invoice to being No Duplicates, you get a 1 to 1 relationship. thanks for it..

PS : this is just my thoughts..and i explain how i do this and that..don't mean that it have to go that way..so if u or others have some suggestion for better design..i'm please to hear it and please give some explanation too why u doing something like that..coz it can makes me better in analysis for problem in the future

thanks alot jp :thumb:

[jp140768]

I'm still slightly confused by the OrderDetails and ProductDetails.

OrderDetails, is a list of the products used within an order. Primary Key being Order Id and ProductId. This table has two fields price and quantity.

Product Details is a list of the products against an invoice. Primary Key being Product Id and Invoice Id. This table has two fields price and quantity.

However, you have said that there can only ever be one invoice per order, so in effect both tables are holding the same information - I think.

To get around needing two sets of order tables, you could make your company a customer on your customer table. To identify your company from all other customers, you can do this one of two ways:
1). Set up a table, which contains the customer number of your company - you can then use this to either select only customers which are your company (you may have more than one branch), or select customers who are not your company.
2). Put a flag on each customer record which indicates whether it is your company or not.

I'm not that good with the quote thingie but you said:
isn't that DueDate and isPaid depend on InvoiceID rather than OrderID?

If we can only have one invoice per order as stated in your text, then the invoice id and order id are for all purposes the same thing.

Hope some of this helps.

[erickwidya]

yes, OrderDetails and ProductDetails is holding the same data (for Price and Quantity). i end up using this two table is to calculate the Quantity of a Product everytime it get sold..it's the 'job' for Quantity at ProductDetails..(for the Price thing, yes it can get from OrderDetails or Invoice but i rather at the same table -> ProductDetails)

..or select customers who are not your company. sorry..don't get it..

If we can only have one invoice per order as stated in your text, then the invoice id and order id are for all purposes the same thing. yes it can say like that..i separate this coz the OrderID is a prove that we make an orders to Supplier and InvoiceID is a prove that we already got the Products that we ordered..-->am i assuming right?

i'm also have doubt about same table that hold same information..but it is the best that i can think of (like the Orders and CustomerOrders..i've done that coz for the 'simpler' thougts..that if i done it at same table..it'll be difficult to trace in the future if the record 'grows')

could u rearrange my dbase the way u think it right? and give me some explanation why u do this and that?
i discuss this too at vbForums (http://www.vbforums.com/showthread.php?s=&threadid=311144)
i'm so confuse now..:ehh:

thanks again

[jp140768]

Sorry its taken so long to reply.

To be honest there is nothing wrong with the structure of your database, it is well laid out, and if it suits your requirements then I would go with it.

The only reason I was suggesting that you roll some of the tables into one, is to make like easier when you are trying to get the information back out of it - but again, perhaps your application design has already taken care of that.

Its difficult to comment on the database, without knowing exactly how the application will function.

[erickwidya]

that's ok..i'm gratefull that u willing to see the dbase and make a suggestion

well actually i'm not quite sure what i'm going to make :0 coz my company is used to be make it manual..and now want to make it via application that i'm gonna develop for :cry: and i'm not good at analysis a problem until i found one (usually when i making the program..that's not a good way to go if i have to design the dbase over and over again) that's why i discuss this at the forum..

well i think i have to learn it by doing so that i can see problem at my early step in the future

thanks alot jp :thumb: