Solved Sales Database Relationships (1 Viewer)

[iqJafa]

I still think you need five tables. I don't know what your application does but if your users can be on more than one SocialNetwork, this structure won't work. You removed the Platform table but you still have Platform. in the customer table.

Can you confirm that you will NEVER, EVER want to allow a Customer to have more than one Platform association?

I ditched the platform table and replaced PlatformId with Platform name (Facebook, Instagram, etc).
I can confirm that all customers are allowed to use one Platform to make orders. Should any customer chooses to switch platform, his details will change accordingly.

 

[iqJafa]

Looks really good, 2 questions:

1. What is the Delivery field datatype and what is Delivered field datatype? Are they related in any manner?

2. Is NetAmount calculable from the other fields in tblInvoices? If so, you don't need it. Instead you do the math in a query and then refer to the query when you need that value.

My bad.
1. Delivery I've changed it to Shipping. Data type is a Number which is P&P charge. Delivered is Yes/No field. Now I guess all the three fields: OrderDate, Shipping Date and Delivered don't belong.

InvoiceAmount is the subtotal. We'll add shipping charge to it and take away the discount if any to get the Amount a Customer needs to pay.
The net amount is a calculated field.

 

[Pat Hartman]

I can confirm that all customers are allowed to use one Platform to make orders. Should any customer chooses to switch platform, his details will change accordingly.

This is the kind of thing that can come back to bite you. It's like building an asset management system and a customer telling you that an employee will never have more than one computer. This kind of change is a big problem to fix but very easy to handle if you define the tables to handle "many" of something from the beginning. Would you believe the customer who said his employees never have more than one computer? Think of this as a lesson in defensive programming. Keep the schema as it is and if it turns out that the schema has to change, it will be a big job but more likely, you'll want to take the bandaid route and add a second set of columns for the second platform. That of course will cause a completely different set of issues and work arounds.

I would quietly talk to as many other people as I could before committing to a 1 Customer, 1 Platform model. If you have any doube at all, go with the 1-many schema. You can make the form look like 1-1. That will be easy to change should you need to show more than 1 platform. Everything else will already be in place.

 

[iqJafa]

This is the kind of thing that can come back to bite you. It's like building an asset management system and a customer telling you that an employee will never have more than one computer. This kind of change is a big problem to fix but very easy to handle if you define the tables to handle "many" of something from the beginning. Would you believe the customer who said his employees never have more than one computer? Think of this as a lesson in defensive programming. Keep the schema as it is and if it turns out that the schema has to change, it will be a big job but more likely, you'll want to take the bandaid route and add a second set of columns for the second platform. That of course will cause a completely different set of issues and work arounds.

I would quietly talk to as many other people as I could before committing to a 1 Customer, 1 Platform model. If you have any doube at all, go with the 1-many schema. You can make the form look like 1-1. That will be easy to change should you need to show more than 1 platform. Everything else will already be in place.

I didn't quite understand the point here. So, I should have a separate table for CustomerName and Platform? But what would the PK be here? And what is the FK in Customer's table?

 

[mike60smart]

Hi

No what Pat is saying is that a Customer may have 1 or More Platforms.

To manage this you would create a table called tblCustomerPlatforms as follows:-

tblCustomerPlatforms
-CustomerPlatformID - PK - Autonumber
-CustomerID - FK - (Linked to CustomerID PK in tblCustomers)
-PlatformID - FK - (Linked to PlatformID PK in tblPlatforms)

 

[Pat Hartman]

As I said earlier in #7, the customer name goes in tblCustomerPlatforms since it is not likely that the name will be the same across all platforms.

If you're an employee, it doesn't matter if you mess up the design, you'll just fix if it turns out that people do need to use multiple platforms, but if you're a consultant, you may be held to a higher standard and be asked to fix the problem for free.

 

[gemma-the-husky]

I didn't look at your specification, but I am sure @Pat Hartman is correct.
You have to design a system for the outlier case. If 99% of your customers have one platform, and 1% have multiple platforms, you have to design for multiple platforms. It's not so hard to do this at the outset, but it becomes hard to do it later on . Even if you never get a customer with multiple platforms, it's still better to build it in.

It's similar to considering employee teams where very rarely an employee can be in more than one team, or can be a team leader for multiple teams, or where two people are designated as team leader for the same team. You have to get the data analysis right in the first instance.

Maybe you need to document it, and get it signed off, because clients (eg your boss) will always come back with "we thought you knew", about something that they "always do", but which you couldn't possibly know unless you were told.

 

[iqJafa]

Hi again,

I applied the suggestions to my DB.

CustomerUsrName holds what I used to call CustomerName: cutomer ID in the releavant platforn.
CustomerName will be the customer real name

I haven't deleted the redundant fields in tblCustomers, yet. Just want to make sure I'm on the track.

Thanks

 

[mike60smart]

You do not need these fields in tblCustomerPlatforms
CustomerUsrName
CustomerName

 

[Pat Hartman]

We don't know what you are selling so it is hard to know what makes sense but if you need to tie an invoke to the platform it came from, instead of CustomerID, you might need CustomerPlatformID in tblinvoices so you tie the invoice to the customer via the platform it came in on.

You DO need CustomerUserName in the Platform table. Each user can have a different ID depending on the platform. I think this got renamed as CustomerPLTID. So remove this field from customers and move it to platform.

Let's clarify again:
CustomerName is the real name of a person - too bad you didn't take my advice on breaking this up.
CustomerPLTID is the user name on a platform like mike60smart
Address is the customer's billing address - too bad you didn't take my advice on breaking this up.
Assuming that a phone is mobile caould cause a problem and not allowing for a non-mobile is short sighted. I personally never give my cell number to anyone unless I am meeting them somewhere. I never give it to companies and I always tell my friends who have the number not to call it unless we are on the way to meet. I simply refuse to be tied to a phone and the cell phone is for my convenience not anyone else's.

 

[iqJafa]

We don't know what you are selling so it is hard to know what makes sense but if you need to tie an invoke to the platform it came from, instead of CustomerID, you might need CustomerPlatformID in tblinvoices so you tie the invoice to the customer via the platform it came in on.

You DO need CustomerUserName in the Platform table. Each user can have a different ID depending on the platform. I think this got renamed as CustomerPLTID. So remove this field from customers and move it to platform.

Let's clarify again:
CustomerName is the real name of a person - too bad you didn't take my advice on breaking this up.
CustomerPLTID is the user name on a platform like mike60smart
Address is the customer's billing address - too bad you didn't take my advice on breaking this up.
Assuming that a phone is mobile caould cause a problem and not allowing for a non-mobile is short sighted. I personally never give my cell number to anyone unless I am meeting them somewhere. I never give it to companies and I always tell my friends who have the number not to call it unless we are on the way to meet. I simply refuse to be tied to a phone and the cell phone is for my convenience not anyone else

We don't know what you are selling so it is hard to know what makes sense but if you need to tie an invoke to the platform it came from, instead of CustomerID, you might need CustomerPlatformID in tblinvoices so you tie the invoice to the customer via the platform it came in on.

You DO need CustomerUserName in the Platform table. Each user can have a different ID depending on the platform. I think this got renamed as CustomerPLTID. So remove this field from customers and move it to platform.

Let's clarify again:
CustomerName is the real name of a person - too bad you didn't take my advice on breaking this up.
CustomerPLTID is the user name on a platform like mike60smart
Address is the customer's billing address - too bad you didn't take my advice on breaking this up.
Assuming that a phone is mobile caould cause a problem and not allowing for a non-mobile is short sighted. I personally never give my cell number to anyone unless I am meeting them somewhere. I never give it to companies and I always tell my friends who have the number not to call it unless we are on the way to meet. I simply refuse to be tied to a phone and the cell phone is for my convenience not anyone else's.

CustomerPLTID: Customer User Name in the relevant platform.
CustomerName: Now I have changed this into CustomerFirstName and CustomerLastName as per your suggestion. Also I detailed the address into AddressLine1 and AddressLine2, city and Province. Mobile is now Phone and Mobile.

 

[Pat Hartman]

As long as CustomerPLTID got moved from the customer table to the CustomerPlatforms table. That's fine but you didn't answer the question about whether or not the Invoice needed to link to the platform. If the user has two platform records, do you care which one he used to make the purchase?

 

[iqJafa]

As long as CustomerPLTID got moved from the customer table to the CustomerPlatforms table. That's fine but you didn't answer the question about whether or not the Invoice needed to link to the platform. If the user has two platform records, do you care which one he used to make the purchase?

Although we haven't encountered any such purachase yet, but hypothetically speaking if we did have, we're going to deal with the customer as another person and another account as if you have two accounts on ebay sharing same address and billing details.

 

[Pat Hartman]

That is certainly a method but I would recommend against it. I don't understand why you wouldn't want to be able to identify all business you do with the same customer. But, it's not my company.

 

[iqJafa]

I have re-defined the relationships. But couldn't figure out how data will be mapped between tblcustomers, tblcustomerplatforms and tblplatforms.

 

[Pat Hartman]

Try not to be sloppy with your names. Shouldn't ItemID in the product (not product details) table be related to ItemID (NOT Item) in the OrderDetails table? And if you call the table Product, why call the PK ItemID rather than ProductID? Sometimes it's hard at the begining of a project to settle on standard nomenclature. Try to get the users to tell you how they think of this entity and use their name. In the greater scheme of things, it doesn't matter whether you use Item or Product as the entitty name but you want your labels and column names to be consistent to avoid unnecessary errors or brain strain as you try to figure out what you called some attribute in which context.

 

[iqJafa]

Thanks to everyone who responded to this thread. The issue is now solved,
Special thanks to @mike60smart who helped me by redesigning the database.

 

[mike60smart]

The new Relationships are shown as follows:-

 

[Pat Hartman]

I guess it was so long ago you forgot #7 and #19 and all the others.