Database design, how to setup tables

Question

I have a simple ecommerce type site that will have users with accounts, orders, and contact/billing information for those users. I just wanted to know the best way to setup these tables from an efficiency and logical point of view. So far I have basically two different entities, users and orders. Users will have basic account information like name and password, Orders will contain all the information about the certain order.

My question is, should I include all contact/billing information within the User table or make two separate tables to contain contact and billing information?

Answer 1

I think the contact information should be in the users table, there's no need to normalize it down to dedicated zip code tables etc. Its the same with the billing information, if they are perhaps creditcard number or something like this. If billing information means a certain plan, then i would put the different available plans into a seperate table and "link" it by a foreign key.

Answer 2

IMO, they should be part of the users table. There are no users sharing the same contact information, therefore the tables will have the same number of rows. In that case, I don't see a reason to make the contact and billing informations into a separate table.

Answer 3

If you'll be looking up contact and billing information at the same time and you'll rarely be using just one of them then there is no use separating them into different tables since you'll be joining them anyway.

On the other hand, if you often only lookup one of contact or billing then performance might benefit from the separation.

I, personally, would not separate them since I can't foresee many times where I don't want both of them.

Answer 4

Contact, billing, and shipping (if needed) information are logically separate. From a pure normalization point of view, an address should be a separate entity, and you should have a relationship between Customers and Addresses. The most 'correct' approach from a normalization point of view would have a many-to-many relationship between Customers and Addresses, with a Type for differentiation between billing, contact and shipping addresses (assuming it's allowable to have more than one of each).

You also need to consider your products as well. A common approach is to have an Order table, with a Lines table below it (one-to-many relationship from Order to Lines). Each Line will reference a single Product, with a Quantity, a Price and any other relevant information (discount, etc.). Simple pricing can be accomodated with a price entry in the products table; more complex pricing strategies will require more effort, obviously.

Before anyone says I've gone too far: 'simple' sites often evolve. Getting your data model right beforehand will save you untold agony down the road.

Answer 5

You might have to think about this a little more:

Users have a contact and billing address, what about shipping and other addresses? Is it possible to have two users from one company, each with different contact addresses but the same billing address? Is it possible for one user to have multiple billing addresses that they will choose at the time of order?

Then when it comes to your orders, what happens if a customer updates his billing address, and then you pull up an order from before the billing address changed. Should the billing address on the old order be the new one, or the one from the time of the order?

If the problem is as simple as you describe, and a user simply has contact and billing addresses, and there are no other "what ifs", then it makes sense to just put those addresses in the user's table. From my limited experience in this domain, however, you may very well find that addresses need to be entities in their own right, separate from specific users. In fact, it may be that you think of a "user" as a "contact address".

Answer 6

Will you have cases where a "user" could have multiple contacts?

I've had database designs where a "customer" could have multiple contacts, and in such cases you should maintain a separate table for contacts. Otherwise, as the others said, you can keep the users and their contact information in the same table since you'll always be querying them together.

Answer 7

How do I setup a table like this? Im creating my first website and I want to add a "community" page for everyone top be able to communicate in. Im in need of a table VERY similar to this but cant seem to find out how they are made. Can someone help??