OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Customer db - How to Organize, customers have 70+ products
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Mon Jun 13, 2011 9:06 am    Post subject: Customer db - How to Organize, customers have 70+ products Reply with quote

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
Back to top
View user's profile Send private message
sainttomn
OOo Enthusiast
OOo Enthusiast


Joined: 18 Feb 2011
Posts: 131
Location: Two gangster planets and a cowboy world

PostPosted: Mon Jun 13, 2011 10:49 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jun 13, 2011 11:04 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Mon Jun 13, 2011 11:37 am    Post subject: Reply with quote

Thanks Villeroy and sainttomn. I'll give those links a look.
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Mon Jun 13, 2011 12:48 pm    Post subject: Reply with quote

Does this look right?



Edited img tag - floris v, moderator
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jun 15, 2011 11:54 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Tue Jun 21, 2011 10:13 am    Post subject: Reply with quote

Thanks Villeroy. Smile
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Tue May 01, 2012 11:09 am    Post subject: Reply with quote

sainttomn wrote:
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 know this is an old topic, but we're now revisiting this idea.
I have a simplified version, with only about two hundred entries instead of several thousand that I need to enter. I can enter by hand if needed.
Question is at the bottom.

I have so far:
1. Customers table
- AssetID (primary key), CustomerName, Notes
2. Orders table
- OrderID (primary key), OrderDate, PO#, Serial#
3. Products table
- ProductID (primary key), ProductDescription, ProductName, Quantity

Purpose
I will need to run queries along these lines -
In the past 5 years, what customers bought "Product A" but not a maintenance plan?

Question
How do I hook up the tables, and which field in one table attaches to which field in another table?

Thank you in advance if anyone is able to devote any attention to this. I am researching, as well, but databases are confusing to me Smile

EDIT:
Is this right?


Removed spammy signature - floris v, moderator
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Tue May 01, 2012 3:20 pm    Post subject: Reply with quote

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.


Last edited by Billyray on Wed May 02, 2012 6:08 pm; edited 1 time in total
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Wed May 02, 2012 12:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Wed May 02, 2012 12:45 pm    Post subject: Reply with quote

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

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

Removed spammy signature - floris v, moderator
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Wed May 02, 2012 6:40 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Thu May 03, 2012 7:57 am    Post subject: Reply with quote

Billyray wrote:
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.


So the Orders table would not have a ProductID field?
Would the Order table have things like ProductName and Serial#? Or does that all go in an Order Details table?


Thank you SO much for this. This type of explanation is really breaking it down in a way I can understand.

So how does this look so far?
http://i.imgur.com/CCFvs.jpg

Removed spammy signature - floris v, moderator
Back to top
View user's profile Send private message
Billyray
OOo Enthusiast
OOo Enthusiast


Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio

PostPosted: Thu May 03, 2012 10:54 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dpmpls
General User
General User


Joined: 10 Jun 2011
Posts: 23

PostPosted: Mon May 07, 2012 7:30 am    Post subject: Reply with quote

Billyray wrote:
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.


There can be many products on one order. So, I would make an Order Details table, use OrderDetailsID as the primary key.
Then OrderDetailsID would connect to the Orders table via OrderID (OrderID being the foreign key)
Maybe it would help if I thought in terms of master/slave.
Order Details table is master, Order table is slave.

Is this correct?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group