Advice needed with developing database application.

[lkallas]

Hello!

I am developing an application for my employer. I am not a programmer but I do it as a hobby.
The story is as follows: we have a store that sells lawn tractors and riders. Every lawn tractor has a serial number, product code etc. I am trying to make a database driven application that stores serial number, product code and other data. I know how to do it with access database and how to add search functions and so on. But the thing is that I know how to do it when a customer buys only 1 product. How should I design a database or the application when a customer buys more than 1 machine, let's say 10 for example? Should I do 10 independent databases and link them somehow to the customer? For example: a database (only)with customers data, then a database for his/her purchased machine nr. 1 and then a database for purchased machine nr. 2 and so on.

What is the most simple solution to this problem. Help me out, I am all out of ideas.

[Reverend Jim]

Maybe not as simple as you'd like but what I'd do is

Inventory

• ProductID (PK)
• ProductCode
• SerialNumber
• Cost
• ProductName
• Description

Customer

• CustomerID (PK)
• CustomerName
• CustomerAddress

Orders

• OrderID (PK)
• CustomerID (FK)
• OrderDate
• Tax
• TotalCost

OrderItems

• OrderID (PK/FK)
• LineID (PK)
• ProductID
• Quantity
• TotalCost

PK = Primary key, FK = foreign key. ProductID, CustomerID and OrderID would be IDENTITY fields (generated by the database or application and guaranteed to be unique). These could start at 1 and auto-increment. LineID could start at 1 for each new order and auto-increment.

Each order has its unique OrderID. The OrderItems table contains all of the items ordered by the customer. For this table you use a compound key (OrderID/LineID) so guarantee a unique key value. Some fields (like CustomerAddress) could be split into multiple fields, say, for city, street, etc. You'd probably add other fields such as telephone number. Orders would also likely have fields for OrderStatus, ShippingAddress, DeliveryDate, etc.

[lkallas]

Hi again!

New problem has come up. I have 2 tables that are related to each other: Client table and Product table. They are related to each other with Client ID.
I have designed my application to display data in textboxes and divided textboxes in to 2 groupboxes "Client" and "Product". Client group has textboxes with client data and Product group has textboxes with product data.
I added 2 buttons: "Previous" and "Next" and made them work with following code:

Private Sub btnNext_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnNext.Click
BindingContext(Dataset1, "Product").Position = BindingContext(Dataset1, "Product").Position + 1
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
BindingContext(Dataset1, "Product").Position = BindingContext(Dataset1,"Product").Position -1

End Sub

Now the problem is that it navigates through all data. But what I need is that these buttons will navigate only through product data that has the same Client ID.

[Reverend Jim]

Client table and Product table should have no fields in common. They should be related only through the OrderItems table. The purpose of the Client table is only to contain information on the client and to map the client to a ClientID. Ditto for the Product table. If you want to get picky, I should have split Inventory into two tables as follows:

Product

• ProductID (PK)
• ProductCode
• Cost
• ProductName
• Description

Inventory

• ProductID (PK)
• SerialNumber (PK)

[lkallas]

Thanks for replying!

Right now the database isn't an issue as much as the navigation buttons.

How do I make them navigate only through the data that has the same ID number.

The code that I posted earlier navigates through all data regardless what the ID number is. But what I want is that it navigates through specific data that has given the same ID number.

Hope it isn't too confusing....
Self learning is a braincracker.....

[themaj]

lkallas,

First of all you should stop and rethink your plan after reading Jim's responses. I did this exercise 5 years ago in VB6 and the initial DB design is very critical! It's really, really hard to back peddle after the DB starts taking on data.

You need 1 (one) DB with multiple tables as Jim discussed. Your tractor (Product) and your customer (Customer) aren't related until the transaction (Order) occurs. Your John Deere and your John Smith should only be connected by a transaction in your Order table. Otherwise you are building a HUGE, wasteful and inefficient collection of electronic paper.

Please save yourself a lot grief and frustration by pausing your development and reconsidering your DB structure.

In your present situation however (from what I see) you need to populate those DataSets with a query that limits the results to that Customer_ID instead of all your customers. It looks like you're 'scrolling' through a single table that has ALL the data on ALL transactions...like its a line item spreadsheet.

If you're looking for what John Smith bought; that's one query.
If you're looking for who bought all the John Deere tractors; that's another.

Hope this helps but let the grass grow a little and rethink your database.

Good luck and look forward to hearing more :)

[Reverend Jim]

The idea of setting up the tables to avoid duplication of data is called "normalization". If you store the same data in more than one table then you run the very real risk of the data getting out of sync (plus you waste space). For example, if you store the customer address with each order and the customer moves then you have to update each record that contains that phone number. On the other hand, if the address is stored only once in the Customer table, and the customerID is stored in the Orders table then you only have to change one record in the Customer table.

[lkallas]

Thank you for replying!
I have made some research on (access) database and its relationships and normalization.
What I still don't get is that why I need Transaction table as the so called "Middleman" for showing what machines(products)certain client have.

What I have right now:

Client table:
Client ID (Autonumber), Primary key
Name
Telephone
Address

Product table:
Product ID (autonumber), Primary key
Client ID (number), Foreign key
Product code
Product serial
Engine type

Now my relatsionship is built like so: Client ID(primary key) from Client table is related to Client ID (foreign key) from Product table.

Now when I run Querys in access it works fine. But now tell me why I need the "Transaction" table in between them to normalize data syncing? I don't get the idea :(
It's so stupid to be stupid :D

P.S!
I am an Estonian, so maybe my english isn't very good and I don't understand all you are saying or maybe I don't express myself clearly enough. Apologies for that :)

[Reverend Jim]

I don't know what you mean by normalize data syncing.

You have many products so you have a product table with one entry for each unique type of product. You can have many of each product so the product table may or may not contain a quantity field (usefule to see how much inventory you have). If you need to keep track of each item (say, for example if you need to track serial numbers) then you would need a table for that which relates serial numbers to product codes. You have many (I hope) customers so you need a table with one record for each customer. You have many (again, I hope) orders so you need a table with one record per order. Each order may have multiple items so you need an OrderItems table. Each record in this table has one record per item. Each record has to have an order number to "connect" it to a particular order. An order has information such as billing date, shipping date, total, tax, etc. You can't duplicate that information for each item which is why the items are in a separate table. It's more obvious if you draw pictures of the tables with lines showing the relationships. I could throw something together if you think that would help.

[themaj]

lkallas,

One reason you want/need a Transaction table is so you can track all of your transactions. That may sound stupid but remember that when the factory sends you more tractors it needs to be added to the Inventory. That's a transaction but it is not an Order.
Orders are revenue (money in - product out) but a transaction between you and the factory is a cost (money out - product in).

Note that if you have more than one source for your various tractors you should also have a Vendor table.

So a Transaction table should have SalesOrders & PurchaseOrders.

I'm not going to complicate it very much with all these tables but attached is a sample of the beginning of your database. I did not include all the tables but it's a start.

Note that I used both an ID field (autonumber) and a TableID field (text). I do that because sometimes companies use an alphanumeric ID for these. If you don't than either remove the field or just use the autonumber values for both fields.

This is a basic sample, I did not include all the tables & fields that Jim suggested but I do agree with him on the structure.

Hope this helps and JIM, please don't hesitate to jump in and comment.

[Reverend Jim]

The only (minor) detail that is missing is on the line ends. Typically, lines end in either a single or double arrow to indicate the nature of the relationship as one-to-one or one-to-many. For example, the a line connecting the Orders table to the Customers table would have a single arrow at the Customers end and a double arrow at the Orders end to indicate you can have many orders for each customer. Thanks for providing the picture.