How can i improve this design (1 Viewer)

[smaviddavid]

Hi, I made a post explaining a database, but I have now came to a conclusion on the database structure; If somebody could look and comment on my listed design it'd be great! And feel free to ask for info, I always miss things :banghead:

Table Structure

• Customer Details (Person 1 & Person 2)
  • Ring order
    • Payment History of Order
    • Order Tracking (Workshop, Setters, Assoy etc)

Customer Details
This table will contain both person 1 and person 2's for their wedding rings to be associated to the same ID number, I felt this was best option as it follows their current DB, and it wasn't so frowned upon here as to people I spoke to previously, so I felt this is best way to keep in tack with their design.

• Client ID (Primary key Auto number)
• Person 1 Details
• Person 2 Details

Ring Order
This is where the order of ring or jewellery is populated, with various fields etc. size pattern. Which will allow a report to be generated for the workshop.

• Client ID Relationship
• Order Number (Primary Key)
• Ring Details, size, pattern, etc.

Payment
Payment, now as its a small business, and rings aren't cheap they allow montly payments prior to completion to ease one final lump figure. They currently track this on paper and have lost £50-100 in some places of incorrectly logged payments. The customer is always right when you run on purely reputation.

• Order Number & Client relationship
• Payment value & type
• Remaining value

Tracker
Obviously, tracks ring location, whether its at the hall markers etc., and when its completed and sent.

• Order Number & Client relationship
• Location of product

Now, hopefully that makes sense, I will want to produce a lot of queries & reports off this which I assume is possible, although I will explain those once the basic table/form construction is done.

My potential issue is, that a couple may raise 2 orders for person 1 and person 2 wedding rings, and often process these as 1 payment. Is it possible to associate two orders into 1 payment table that is displayed on both orders, a flag showing its associated to two orders?


Thanks in advance!

 

[Ranman256]

Payment table would need
PAYID field (autoNum)
PayType
CheckNum

So you can have many orders on 1 payment. Like the the same check# paid for 2 orders.

 

[plog]

You gave an outline of a design and from what I can infer, it's wrong. If you could post the actual layout of the tables that would help.

From your explanation, I believe your Customer Details table looks roughly like this:

ClientID, Person1Name, Person2Name, Person1DOB, Person2DOB, ...
1, Steve Smith, Sally Jones, 2/3/1998, 4/1/1999
2, Larry Gold, Betty Welsh, 7/8/1988, 12/3/1994

If my understanding is correct and you have fields named with a prefix like 'Person1' and 'Person2', then you are doing it wrong.

I also suspect an issue with the Payment table--the [Client Relationship] field is confusing me. I know [Remaining Value] shouldn't be anywhere--its a balance and should be calculated from initial cost minus all payments.

Again though, post your actual structure not just broad strokes. Some sample data would be helpful as well.

 

[smaviddavid]

Payment table would need
PAYID field (autoNum)
PayType
CheckNum

So you can have many orders on 1 payment. Like the the same check# paid for 2 orders.

Ah ok, so rather than linking an order to another order, I would link them both to 1 payment.

You gave an outline of a design and from what I can infer, it's wrong. If you could post the actual layout of the tables that would help.

From your explanation, I believe your Customer Details table looks roughly like this:

ClientID, Person1Name, Person2Name, Person1DOB, Person2DOB, ...
1, Steve Smith, Sally Jones, 2/3/1998, 4/1/1999
2, Larry Gold, Betty Welsh, 7/8/1988, 12/3/1994

If my understanding is correct and you have fields named with a prefix like 'Person1' and 'Person2', then you are doing it wrong.

I also suspect an issue with the Payment table--the [Client Relationship] field is confusing me. I know [Remaining Value] shouldn't be anywhere--its a balance and should be calculated from initial cost minus all payments.

Again though, post your actual structure not just broad strokes. Some sample data would be helpful as well.

Thanks for the reply, I will upload a copy of it tonight with some sample Data in it.