Creating a DB, for tracking orders with relationships between clients (1 Viewer)

[smaviddavid]

Hello, first post in long time here, restarting a project i abandoned.

Firstly, my access knowledge is limited, although i taught myself a lot last time, and eager to relearn.

My database is for tracking orders for wedding rings, and currently is done using an Access DB. Now its over 7 years old and lacks features which can aid pushing forward, reports, queries, etc, its very simple.

As its for wedding rings, there are two customers (him and her, or her/her etc...), two rings. pretty straight forward, set each as their own customer and own orders. But how the old DB was laid out, these were stored as one customer and listed orders. I think this is bad practice, also for generating reports etc, it means having to search down two sets of data within a row.

Ideally, i want them as separate customers, but the same, if that makes any sense, I'm sorry if it sounds confusing.

SO! - Is there anybody here who could help me with establishing how i would put this together with various tables? My initial thought was linking the customers together with a "CoupleID" and then producing a form with queries for the two customer ID's associated to the present CoupleID.

I will stop waffling, and hopefully somebody here will be able help.

 

[Isskint]

Well my first thoughts, do you receive an order from partner A for a ring and another order from partner B for a ring OR 1 order for 2 rings from partner A +/or B?

You could have separate tables for customer details and orders but how many 'repeat' orders do you get (hopefully none) My initial direction would be an OrderDetail's table with fields for both partners details (partAname, partBname, partAaddress, partBaddress etc), delivery address (or flag which partners address), required date etc. Then an OrderItems table {with a FK on the OrderDetail table} for each ring.

Perhaps if you could give us something from the current DB (relationships or attach the DB) it would give us some focus.

 

[smaviddavid]

Well my first thoughts, do you receive an order from partner A for a ring and another order from partner B for a ring OR 1 order for 2 rings from partner A +/or B?

You could have separate tables for customer details and orders but how many 'repeat' orders do you get (hopefully none) My initial direction would be an OrderDetail's table with fields for both partners details (partAname, partBname, partAaddress, partBaddress etc), delivery address (or flag which partners address), required date etc. Then an OrderItems table {with a FK on the OrderDetail table} for each ring.

Perhaps if you could give us something from the current DB (relationships or attach the DB) it would give us some focus.

Thanks for the speedy reply. I'm glad you could make some sense of my waffle!

I'll start with the second part first, we do get repeat work actually! eternity rings, and a new thing is to get a ring with manmade diamonds, and for anniversary's, birthdays buying real diamonds to swap in at a later date (I done this £3k ring for her birthday was only £500, 2 years later, and presents from family, she's got 18/24 diamonds now.) This will be where having the two orders associated, as he will be ordering the diamonds probably for her, so its his order, for her ring, so seeing her ring in his orders is where this would be beneficial.

The current DB is I believe (its not for me its for GF's parents) is how you say partnerAName, BName etc.. however I was informed this is bad practice? I also struggled to get my head around a form that would display 2 people, but also option for 1 person (I guess i'd leave second field blank?).


When they receive an order, both rings are usually on the same order form, so I suppose the method we are discussing is most appropriate, again I only thought of another due to comments from other people. Sometimes though orders are separate, altho this is no problem.

I suppose by adding an option on the order to associate I could associate an order.

For example, I want to search the DB/Tracker for Partner A/B who's aniversay is in March, and their partners ring has cubix in it rather than natural diamonds. Then I want to email them all. This is why I thought of storing orders separately.

Again, I'm thinking half of this as I go so if it makes no sense anywhere just ask.

I'll get a copy of their DB / sample of it when I visit their office next.

 

[gemma-the-husky]

I think you need to decide who the customer is.

it's probably unusual, but you probably could arrange to have an order/and or an invoice with multiple owners. Then you could search for orders by a person, and pick up the same order for each customer. You would probably need a 1-many order-clients table to store the (potentially) multiple clients fro each order.

alternatively, you might have a "main customer" for an order but attach the second customer as a linked customer to the main customer.

perhaps look for a holiday booking dbs - it sounds similar to a lead client on a holiday booking. See how they do it. or see how solicitors or financial institutions to see how they manage joint clients.

do you really need to store both customers as order owners, in your case? Surely a second customer would know the main customer and order number for any order they wanted to enquire about anyway.

 

[smaviddavid]

I think you need to decide who the customer is.

it's probably unusual, but you probably could arrange to have an order/and or an invoice with multiple owners. Then you could search for orders by a person, and pick up the same order for each customer. You would probably need a 1-many order-clients table to store the (potentially) multiple clients fro each order.

alternatively, you might have a "main customer" for an order but attach the second customer as a linked customer to the main customer.

perhaps look for a holiday booking dbs - it sounds similar to a lead client on a holiday booking. See how they do it. or see how solicitors or financial institutions to see how they manage joint clients.

do you really need to store both customers as order owners, in your case? Surely a second customer would know the main customer and order number for any order they wanted to enquire about anyway.

Hi Gemma,

Thanks for those ideas, I suppose your right, currently the "main customer" is always the women, so I could add a "sub customer" so to speak.

Maybe I am underestimating the power of queries and reports. With this I'm going to go speak with them and get a definitive idea using iput from the pair of you and will get back when they've given me an idea on how they want to progress.