someone to review my database

[hatman]

ok i have another question? i found another database on my computer. it seems to me to be more user friendly and i have no idea when i made this one.My question would be which one would you use ?
also which database has the better set up for my products?
is there any tables you would adjust? and if so what should be done?
again i am looking to make a database for customers,products and orders?
do i have my orders and invoices set up ok?
i am a little confused as to setting up my orders and invoice tables.


thanks in advance

[Xipooo]

ok i have another question? i found another database on my computer. it seems to me to be more user friendly and i have no idea when i made this one.My question would be which one would you use ?
also which database has the better set up for my products?
is there any tables you would adjust? and if so what should be done?
again i am looking to make a database for customers,products and orders?
do i have my orders and invoices set up ok?
i am a little confused as to setting up my orders and invoice tables.
thanks in advance

I like the table structure of the one you've been working on better than the one you've found. There is also a LOT more data in the one you've been working on.

Invoices are orders which have been placed and billed. An invoice is generally created after an order has been completed and the goods or services have been or are being delivered depending on your business practices.

It is a good idea to have an OrderID in your Invoices table and an InvoiceID in your Orders table so that you can look to either one to find the corresponding counterpart. You CAN have it where only one of the two tables points to the other, but it makes for more complex queries down the road.

Invoices are usually broken up into two different tables. A headers table and a details table. Your tblInvoiceHeaders table should be the general information about the invoice. Things like CustomerID, AddressID, ContactID, OrderID, Total amount for the order, taxes, etc. Your tblInvoiceDetails table should be specific line by line details about that invoice. Things like the ProductID, ProductPrice at the time of the sale, Quantity, LineNumber, and a reference to the InvoiceHeaderID.

Again, this is a suggestion. It is not a requirement.

[orange]

I haven't looked at your example databases, but I would suggest that if you designed a particular database, based on your business , then that database is more likely to fit your needs better than something you "found" on the internet. The only condition I would add is that you have identified your business; understand the business rules; have built and tested your database design against those rules.

[hatman]

yes i think the first one works better too. on another note do i have my tables set up right to keep going.as you can see in Ordersdetail table i am using OrdersID as my invoice number. Is this the correct way to do it? If not can you help me set it right?
if you have any suggestion on a better way please show me a way you would set it to. again i am new to this and i am just using paper copies to enter my sales.

will i be able to run a query to see how much a certain customer has purchased for the year? also could i break it down to say jan,feb etc?
as always thanks for you advice.
greatly appreciated.hatman

[Xipooo]

yes i think the first one works better too. on another note do i have my tables set up right to keep going.as you can see in Ordersdetail table i am using OrdersID as my invoice number. Is this the correct way to do it? If not can you help me set it right?
if you have any suggestion on a better way please show me a way you would set it to. again i am new to this and i am just using paper copies to enter my sales.

will i be able to run a query to see how much a certain customer has purchased for the year? also could i break it down to say jan,feb etc?
as always thanks for you advice.
greatly appreciated.hatman

We're starting to venture into tutorials land. We really aren't going to be able to get you what you need if you don't educate yourself a bit more on database normalization. I suggest you do some reading and video watching on normalization techniques. There are lots of articles on it. Here is one by Microsoft for Beginners: http://support.microsoft.com/kb/283878

I also go over some basic database design in my tutorial series. http://www.youtube.com/playlist?list...03jQ_t9nFV737s

[orange]

I agree that you have to gain some knowledge/experience in database design and normalization.

Watch these videos (youtube)
Video tutorials:
These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck.