need suggestion for normalization (RESOLVED?)

[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 effort

[dRAMmer]

To create a one-to-one rel. click on the referenced key first and drag it to the foreign key (column in the foreign table).

That is, click OrderID in the Orders Table and drag it to the Invoice table.

[erickwidya]

thanks dRAMmer

That is, click OrderID in the Orders Table and drag it to the Invoice table.

it display one to many (from orders to invoices)..
and i think access never mind which key i drag first..coz if i do at opposite way (from invoice to orders..)still got relationship like the one above..
if the OrderId at Invoices is Primarykey..then it got one to one..but it seem doesn't right since the InvoiceID should be the PK for Invoices table..

do i make sense?

[dRAMmer]

Ah okay, sorry for the mistake. Disregard my first reply, what you should do is create an INDEX on the Invoice table and make sure that OrderID does not allow duplicates. That will do.

[erickwidya]

thanks..that should do the trick..

regarding my 1st question..is my design is right?
(my doubt for the ProductDetails Table that have InvoiceID in it..should i drag a relation to Invoices table?)

got InvoiceID in ProductDetails is because that one Product can have many Price..so if i got InvoiceID means that i already got that Products and i can add new record at ProductDetails with different Price..is this right?
i'm not familiar with this *Invoice - Order* thing before

PS : sorry for my bad explanation..
regards

EDIT : can RDBMS have a circular relationship..like A -> B..B -> C..C -> D..D -> A?

[Ecniv]

Sorry to butt in...

Your design is um confused, you seem to hold duplicate data?

Here's some of your fields (I think there are probably a couple more...)

Customer name
Customer Order Date
Customer Order Details - Price
Customer Order Details - Quantity
Product Name
Supplier Name
Invoice Date
Invoice Pay due date
Invoice Paid


You've done a good job putting most of the selections together though, so heres a possible way - up to you to look at and refine...

tblCustomers - holds all customer specific info
CustomerID - auto - pk
CName

tblProducts - Product specific details
ProductID - auto - pk
PName

tblSuppliers - Supplier specific details
SupplierID - auto - pk
SName

tblSupplierToProducts - Holds a link between the supplier and the product
SuppToProdID - auto - pk
SupplierID - number
ProductID - number
CurrentPrice - number

tblCustomerOrders
CustomerOrderID - auto - pk
CustomerID - number

tblCustomerOrderDetails
CustomerOrderDetail - auto - pk
CustomerID - number <<< this doesn't have to be included... but you may need it
CustomerOrderID - number
ProductID - number
SupplierID - number
Price - number
Quantity - number

tblInvoices see Customer Orders
tblInvoiceDetail see Customer Order Details

This is only a suggestion which you could build upon...


Vince

[erickwidya]

Your design is um confused, you seem to hold duplicate data?

i think what u mentioned is Orders and CustomerOrders, etc right?

yes it is holding data..the CustomerOrder is hold order from customer..and Orders is my order to supplier..
i'm developing app that act as 'middle man' between customer and supplier..so that's why the table contain duplicate data (it's for easily tracking..so if it concern about Customer than i check Customerbla-bla table..and that the same for supplier sake)

i'll check ur design Ecniv..thanks

[dRAMmer]

hi erickwidya!
sorry i wasn't able to go through your entire design, I only checked the problem you have regarding the relationship, I'll check it later, if you still need it.

[erickwidya]

thanks dRAMmer..i still need it
if u don't mind..

[erickwidya]

Ecniv..it seems that ur design simillar to mind..but diff in
tblSupplierToProducts (i think this table is for many to many relation between Supplier and Product? well..i forgot to mentioned that 1 product is for 1 supplier and 1 supplier can have many produt --> 1 to M relation, sorry )

regards

[Ecniv]

I don't get the product has one supplier..
Yet the supplier has multiple products...

So I just put in that the supplier can have multiple products... SupToProd table...
Might class as many to many, but I couldn't see why you'd want to go from products to suppliers, still the option is there.


I see what you mean with the customer orders and supplier orders, does make sense.
You could (in theory) have just the one table and a flag field for sent to supplier, but that may confuse things.

Looks good though.


Vince

[dRAMmer]

In addition to vince's design, you could have ProductID and ProductNumber. Use the ID because it's faster to index on a numeric field. Use the Number to get the user's preferred product code. You could then apply this to all your table, always referencing the ID.

Regarding the supplier and product, keep in mind that a product could have multiple suppler and, of course, a supplier always has multiple products.

Also, why do you need to have separate table for Product, or I think you shold rename ProductDetails for I think it's purpose is not in it's name.

Additionally, I think it's better if you make all your primary key a Required field.

[erickwidya]

You could (in theory) have just the one table and a flag field for sent to supplier, but that may confuse things.

that's exactly my first thought when i see the whole design..but as u said it can confuse things if the record is 'grow'

Regarding the supplier and product, keep in mind that a product could have multiple suppler and, of course, a supplier always has multiple products.

yes it is..but in my case..the product always from one supplier..as i said in my preview post

Also, why do you need to have separate table for Product..

it's for the Price things..coz one Product can have one or more Price depends on the day we got that Product (in this case, Invoices) and the InvoiceID at ProductDetails is my thought that if my company already got InvoiceID from Suppliers than we can add new Product at ProductDetails depend on that Invoice which it refer to Orders and OrderDetails..right?

Additionally, I think it's better if you make all your primary key a Required field.

thanks for remind me

btw : can RDBMS have a circular relationship..like A -> B..B -> C..C -> D..D -> A?

thanks

[dRAMmer]

it's for the Price things..coz one Product can have one or more Price depends on the day we got that Product (in this case, Invoices) and the InvoiceID at ProductDetails is my thought that if my company already got InvoiceID from Suppliers than we can add new Product at ProductDetails depend on that Invoice which it refer to Orders and OrderDetails..right?

What are you trying to achieve here? I believe you're wanting to have a transaction in which you want to record the price and quantity updates based on a supplier. If so, then your table should be named differently as it would confuse other developer also working on your project, am i right?

[erickwidya]

dRAMmer : one Product can have more than one Price so that is why i got Product and ProductDetails..could u let me know what the design that comes in ur mind? (for one product that can have more than one Price) so i can earn some experience to help me in the future from this..

PS : plz let me know if my explanation is not clearly enough..

thanks

[dRAMmer]

hi!

why would a product have more than one price if it only has one supplier? If a product, say, was delivered on different times with different price then it would still have one price (as it's last price or current price maybe, depending on how you would define it). Again, was exactly is your goal on these prices? Are you going to use it in a report where you will show the variation in prices from a period of time? If so, then it is more of a transaction table, is this what you're up to?

[erickwidya]

why would a product have more than one price if it only has one supplier?

coz my company wants to know the stock of certain Product with the price of it..so it can sell to the Customer but with Different Price..

Are you going to use it in a report where you will show the variation in prices from a period of time?

not exactly like that..but i want to make a report that show which Products that sold to Customers with the buying Price and the selling Price..so we can calculate the benefit from the different Price..and maybe how much stock left for certain Product, what Price..etc

is this clarify ur question?
NB : maybe my field name is not make clear the purpose of it..

[dRAMmer]

What will happen then if in one of your purchase you ordered the same product twice (this happens when some product was sold to you -by your supplier- with discount) and in the invoice provided by the supplier they also had separate line for it. The user (of your program) will then have to be instructed to either type the invoice item (product) as one and disregard the discount or type the two item as is, in your ProductDetails, you will have same product ID and same InvoiceID (duplicate rows). How would you know then which price to use? By the way if this will be the case you need to have another field called Discount.

I say this because in your table you have InvoiceID as duplicates ok.

coz my company wants to know the stock of certain Product with the price of it..so it can sell to the Customer but with Different Price..

Additionally, if this is one of your goal then you could just have the Invoice table and if it's time to sell the product, you could query on the Invoice table for the Price of the product at a certain date (you have a date field there right). No need to save it in a separate table for that purpose only.

[erickwidya]

i see..

What will happen then if in one of your purchase you ordered the same product twice...

from this..is what ur mean that 1 Invoice can have M Orders? if it is then it's my mistake again not telling from the beginning that 1 Invoice is for 1 Orders only..and for the discount case..the Suppliers never give discount..

if it's time to sell the product, you could query on the Invoice table for the Price of the product at a certain date (you have a date field there right). No need to save it in a separate table for that purpose only.

that's is correct for the Price thing..but what if i want to minus the Quantity of the Product that sold to Customers?

[dRAMmer]

You would normally have to save the Quantity on hand of your product at the present time. You should put all your product info in one table, put there anything you think is related to it, ie Price, Location (Default - if you have multiple location), supplier, etc.

[erickwidya]

You would normally have to save the Quantity on hand of your product at the present time.

can't do this if there's different Price

could u provide the suggestion for the Product table that can have more than 1 Price? so i can figure it out..
thanks

[dRAMmer]

Okay, so you're trying to say here that even though you have 100 apple in all but received 60 of it yesterday with the price of 5 and 40 of it today @ 5.5, you don't have a field to check how much you have in all? So what do you do with this one? Do a select statement and SUM up the ProductDetails table? And if you sell, how do you how much stock you still have?

[erickwidya]

Do a select statement and SUM up the ProductDetails table?

yes..i think of it for displaying total per Products..

And if you sell, how do you how much stock you still have?

coz evertime the user (at my company) entry the data to sold to customer..they have option to select which product to sell..(by its price, invoiceID or OrderID, etc) so it can minus Quantity for the related Product that user select..

[dRAMmer]

coz evertime the user (at my company) entry the data to sold to customer..they have option to select which product to sell..(by its price, invoiceID or OrderID, etc) so it can minus Quantity for the related Product that user select..

I'm not sure about this one, I think you can improve on this. For me, it's not a good way to do this. You can re design your DB a bit and enhance this procedure as well. I have to go now, but I study how you do things and try to suggest things that could make things it better.

[erickwidya]

You can re design your DB a bit and enhance this procedure as well.

welll that's my personal opinion on how the user select which product to sell coz they sell product not in particular order (like FIFO or LIFO)..and i'm free for any suggestion..that's why i post this at the forum..to learn

thanks again dRAMmer

[szlamany]

Originally posted by dRAMmer
Okay, so you're trying to say here that even though you have 100 apple in all but received 60 of it yesterday with the price of 5 and 40 of it today @ 5.5, you don't have a field to check how much you have in all? So what do you do with this one? Do a select statement and SUM up the ProductDetails table? And if you sell, how do you how much stock you still have?

We gave up using "stored aggregated totals" decades ago in our software design. Since SQL has great INDEX capability, always SUM up the values you need at run-time.

Storing a total ends up requiring that you write some fix-up program when to totals go bad - in our experience...

[dRAMmer]

hi szlamany!

Yes, this is true. But then again, the problem would be with too large data. We have clients with enormous data and if the only way get the total is to always SUM up it slows down the whole procedure (that includes the data retrieval), especially if concurrent users are doing the same thing (getting the total). It may be different with your experience, I would be glad if you can share it.

By the way, we have this stored totals because the way TOTAL is computed, in the Transaction table, the TOTAL is not just the SUM of all the records (Qty), there are points in the table where the new OnHand is the value of the current row (if we are browsing) then from there you continue computing the OnHand. So it's like the SUM of what you had before is nullified by the new OnHand. So we couldn't just SUM up the quantity.

[szlamany]

We have customers with 3 millions rows in some tables - and I still would never consider storing a total ever again...

SQL can, with an index, perform the most incredible work in no time flat.

We also SUM up the "budget available" and are able to exclude the "current row" they are on based on it's primary key - so that the "sum total" is exclusive of the row being changed...

We do this for financial applications - for student app's where students are taking a seat in a class - all over our apps.

[dRAMmer]

Good point! Maybe I have to re-suggest my first option to SUM it up and make observations on where to INDEX on best. It's hard where to change major things here where one mistake could rise up tons of complains from client. I admit it, SUM is the best way, you are right here.

And going back to erick's problem

can't do this if there's different Price

could u provide the suggestion for the Product table that can have more than 1 Price? so i can figure it out..
thanks

I guess you're better off to what you are doing right now, just make sure you index on the right column, just a simple tip, it may not always be the (auto-increment) ID.

[erickwidya]

it's too bad that at my company doesn't use SQL SERVER..and i'm personally not familiar with SQL Server thing
the Access here can do what it means to be..

dRAMmer : is what u mean that i go on with the design i already had?

[dRAMmer]

Originally posted by erickwidya
it's too bad that at my company doesn't use SQL SERVER

Still you can manage it with index, I'm just not sure how much it would cost you (performance) to have the total summed up in Access, maybe you could make a benchmark on this and see what will be faster. Just remember though that if you will have what we have (storing the Total in a field), you will have to pay the price of making sure your function that calculates for the total OnHand is updating the table that holds this value.

dRAMmer : is what u mean that i go on with the design i already had?

If you are not having speed problems querying ProductDetails table then maintain your design.

[erickwidya]

If you are not having speed problems querying ProductDetails ...

i'll keep this in mind..thanks

it just i don't 'feel' right about my design..something wrong there..and i can't figure it out which one..
well guess i have to stick with it until i found what's wrong..

well i guest this case is closed? or does anyone or u have any suggestion?

[dRAMmer]

Originally posted by erickwidya
it just i don't 'feel' right about my design..something wrong there..and i can't figure it out which one..

Most often a developer only finds out that his DB design is poor when he can't get the right the data he wanted. Worst, he could get it but with a lots of useless joins and unions
Just remember all the rule in DB designing especially the normal forms.

[erickwidya]

Most often a developer only finds out that his DB design is poor when he can't get the right the data he wanted

u certainly *hit* me on that but of course i don't want to use useless join and unions :0

i'll leave this discuss unsolved 'till monday incase anyone have other suggestion..

until then..


..hmm..the defintion of 1st, 2nd and 3rd NF is..where's those books when i need it...

[Ecniv]

So what happened?? Did you get it working?