Customer db - How to Organize, customers have 70+ products

[dpmpls]

Hello all,
I've been searching the net for information, but haven't been able to answer this yet.

How should I organize this db?
I want to create a db of all customers, all products they've purchased, serial #'s, prices, and quantity for each, etc.
Should I create twenty separate product columns in the table? Or is there a better way?
Would like the cleanest way to organize this.
Thank you

[sainttomn]

At the most basic level, I'd suggest a table for each item, and then a "master" or output table:

Customer Table (Name, address, etc)
Product Table (1 column for product name. 1 column for serial#. etc)
Data Table (customer's order history and such)

The nice thing with doing something like this is that you can add new products as they come along, without serious table modification.

Hope this points you in the right direction. More info on what you ahve an what you're working on will help.
_________________
"I'm a loner, Dottie. A rebel."
[Windows 7] [OO.o 3.3] [HSQL 1.8]

The Lazer Cat

[Villeroy]

So you need a list of customers, a list of product and an intermediate table mapping customers to products (sales).
This is called a many-to-many relation or m:n relation.
http://user.services.openoffice.org/en/forum/viewtopic.php?t=40444&p=186046#p186046
http://user.services.openoffice.org/en/forum/download/file.php?id=10978
http://user.services.openoffice.org/en/forum/download/file.php?id=10060
http://user.services.openoffice.org/en/forum/download/file.php?id=8641
http://user.services.openoffice.org/en/forum/download/file.php?id=6362
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[dpmpls]

Thanks Villeroy and sainttomn. I'll give those links a look.

[dpmpls]

Does this look right?



Edited img tag - floris v, moderator

[Villeroy]

No, it doesn't look right.
http://openoffice.org/projects/documentation/downloads/directory/Base/Mid%20level%20Base%20tutorial
Read about foreign keys and primary keys. A primary key is (mostly) a number which identifis one particular table row. A foreign key is a reference pointer to exactly one row in another table. Referencial integrity (the relation lines with a 1-side and a n-side) enforces that you can not enter any foreign key (n-side) that does not exist in the other table's primary key (1-side).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[dpmpls]

Thanks Villeroy.

[dpmpls]

[Billyray]

The "output" table is rather artificial.

What about this:

Customer table (customerID Primary key)
Orders table (OrderID Primary key, CustomerID foreign key)
Order details (OrderDetailsID Primary key, OrderID foreign key
Inventory (ProductID primary key)

All primary keys can be autoincrement fields

Order details becomes your "output" table but makes more sense and which will be the resource for your queries about who buys what etc.

Picture your Orders table displayed on a main form which contains everything relevant to your Order, i.e. date, customerID, Order Number, type of order, etc.

Picture your Order details as line items contained in your Orders Form as a subform. The subform has records which will have the OrderID foreign key, along with the Inventory part number, price, description etc. (just like you would have on a manually written form on the detail lines.)
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[dpmpls]

Hi Billyray,

So all primary keys should be automatically generated, correct?

How do you decide what field to attach to another field in the tables? That's where I'm getting lost. If I link Field A in Table 1 to Field X in Table 4, I don't know why it makes sense.

Removed spammy signature - floris v, moderator

[dpmpls]

Okay, what about this? Does this make more sense?

http://i.imgur.com/ccWXX.jpg

Removed spammy signature - floris v, moderator

[Billyray]

Your new diagram is more problematic.

Your diagram means that each order will have multiple customers! Instead have the CustomerID field in every Order record. The CustomerID field is the foreign key for the Orders table. The orders table still needs a primary key though. That will be needed when the computer does searches and reports based on your queries. USUALLY that key would be a unique Order Number. But it can also be an auto-increment field.

Now you have a one to many relationship with the 1 being on the customer table side and the many being on the order side (just the opposite of what you have diagrammed.) You do have situations where one customer places more than one order, right?

Now with this beginning, you will notice that it is important that you put ALL data relating to the customer (no order data) in each customer record. All of his address fields, all contact fields: phone, email, etc, anything that relates STRICTLY to the customer table. IOW, every field of the Customer record further defines one individual customer. There should be no other data fields. You don't have to worry about "connecting fields" to other tables, the computer will handle that for you, as long as you have at least one key field or Primary key. In your customer table that would be the CustomerID field which can be auto-increment.

Now to Orders. You will treat this table exactly like the Customer Table. Only create data fields that further define each individual order record. Things like a primary key or Order number field, AND (very important) a CustomerID field which the computer will use to link this order to one specific customer. That is the Order table foreign key. The rest of the fields should be specific to one Order ONLY. Things like the date of the order, the amount (or total) of the order, the type of order, whatever makes sense for your business. Your goal should be to design your record fields in such a way, that they are not REPETITIVE. Don't do this: Order 1 field, Order 2 field, Order3 field, etc. to store data about more than one order on a single record. That is where the Order Details table will come in.

But I would recommend that you first of all design your Customer table, and then your Orders Table, and see how that turns out, before progressing to the detail items for each order. Don't worry about anything else, (like how you will be able to get reports or forms done on this data.) That will all come easily IF you have designed your database intelligently.

BTW, all primary keys CAN be auto-increment fields. The important thing about the primary key is that it be unique, never ever repeated in another record.

Foreign keys will link your table to the table "above" it so to speak, if you think of your database as hierarchical. In that light your Customer record would be at the top of the hierarchy. The Orders Table would be under Customers and the foreign key (CustomerID) is what links those Orders records to one customer above it and not another.

One last thing: don't worry about how those key fields will get filled in. Base will automatically ASSIGN the correct foreign key data to your record when you begin creating forms and subforms.
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[dpmpls]

[Billyray]

That's better dpmpls. As far as the Orders table goes, would only 1 product be on one order? Like selling a house or a car? Or would there be more than one item possible on an order?

If it is only possible to have one product on an order, than you can put those fields in the Order table. But if sometimes there are more than one product, than it would go in the Order details table.

Order details will relate to Orders in the same way that Orders relates to Customers. There will be an Order Details ID which is the primary key (auto increment is easiest) and the foreign key for Order ID to connect the Details to one particular order. Then your product id field, description, quantity, price etc. Your Orders table would be the one side of the relationship, and the Orders details table would be the many side.
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003.

[dpmpls]