smaviddavid
Registered User.
- Local time
- Today, 10:34
- Joined
- Jan 14, 2012
- Messages
- 21
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
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.
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.
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.
Tracker
Obviously, tracks ring location, whether its at the hall markers etc., and when its completed and sent.
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!
Table Structure
- Customer Details (Person 1 & Person 2)
- Ring order
- Payment History of Order
- Order Tracking (Workshop, Setters, Assoy etc)
- Ring order
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!