0

In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be more products in the future, but the business won't exceed 30 products. It's easier for me to not create special products table but to add columns to customer table (each new product - new column).

People advised me against it, but I didn't get an explantaion WHY still can't understand why in such case that product's prices has no relation (each customer has his own special price) adding rows to Products table is better then adding columns of products to Customers table? The only benefit I could think of is that customer that has only 5 products won't have to 'carry' 20 nullable products (saves space on server)?

any help would be greatly appreciated

Improve this question
4
  • database-normalization.com
    – Jon Seigel
    Jul 22, 2012 at 19:35
  • 3
    It is usually not easier to add columns. All the DML, DDL, stored procedures, application code etc. needs to know about the new column. Whereas as a new row the code just needs to know about a new value, resulting in far less overall change.
    – Aaron Bertrand
    Jul 22, 2012 at 19:35
  • In the first place database design for the ease of the developer is the worst possible way to design a datbase. You design for data integrity, performance and security and NEVER because you think something is easier. In the second place, take it form those of us who are professional database designers, your proposed scheme is NOT easier to maintain or write code against.
    – HLGEM
    Jul 23, 2012 at 19:36
  • @HLGEM - Believe me I respect what you people say much more than you think, I'm arguing because I want to UNDERSTAND it myself as well, not just BELIEVE you (and I do believe you!), I'm looking for several examples / circumstances where separate products table would be more efficient than products as columns. (And I do have special different table for reports), the products info is just to store the price of product for that particular customer because prices vary from customer to customer..
    – BornToCode
    Jul 23, 2012 at 22:12

3 Answers 3

Reset to default
7

First, it's about normalization. Today you have max 30 special products, tomorrow (or after 10 years for that matter) you might need another extra 5. You will need to write more code, but you will have greater flexibility after all. At least you won't need to code (or design) 30 columns! You can have a schema like this:

CREATE TABLE Product (ProductId...)
CREATE TABLE Customer (CustomerId...)
CREATE TABLE CustomerProductPrice (ProductId, CustomerId, Price...)

In the third table you can put price for product/customer, but remember to create a unique constraint on foreign key columns like ProductId, CustomerId.

Improve this answer
3

You're just starting your work. You'll see many places that the coding will get very complicated. For example, if you don't have products tables, how are you going to write a query that brings the names of the products who costs more that 100$ for all customers? How do you store more data for each product (stock, cost etc..)

Improve this answer
4
  • Well, I know for certain I won't need such queries like products that cost more than 100$ for all customers (and if such far away situation will occur I don't mind writing a long complicated query etc.), I store stock in a special table, I have sells history table (which also has products has columns b.t.w.), I just can't understand why rows will make it easier in case like mine where the products have completely different values I would even say on the contrary that if I had columns for example it would update AUTOMATICALLY in gridview etc.?
    – BornToCode
    Jul 22, 2012 at 19:48
  • 2
    Don't design your database schema based on what you might think will happen automatically when you're doing something very specific in a gridview.
    – Aaron Bertrand
    Jul 22, 2012 at 19:56
  • @AaronBertrand - OK, I can accept that :) but guys please give me some more meat here, I read all those posts about normalization but I just can't find even 1 good benefit from splitting it to another table in this particular scenario, I don't see any potential data anomalies, I'm certain I won't need queries in the style of query that brings the names of the products who costs more that 100$ for all customers etc., so why do I need it? I'm not arguing with you, I'm sure you guys know much better than me because I'm a beginner, but I want to really understand WHY.. Thank you for your patience
    – BornToCode
    Jul 22, 2012 at 20:04
  • @BornToCode: It might not look like you need it now, and maybe you're happy copy+pasting code to handle 30 columns. One day, another company might buy your company and decide to add many more products (and quickly) or migrate the data to a new schema, and your current approach will make this difficult. One day, you might have to generate various aggregate and detail reports on product sales, and your current approach will make things difficult. And maybe someone else will have to do it, and curse your name for the brittle and inflexible mess they've inherited.
    – FrustratedWithFormsDesigner
    Jul 23, 2012 at 19:16
3

Let's go with the assumption you add 30 columns to your Customer table. 

CustomerId|Name|Address|Product1|...|Product30
10        |Bob |Here   |5.32    |...|

A month later, Bob returns and says "by golly, Product1 was miraculous, I need more". So now what? How do you track that repeat business? Do you care?

If you don't care then continue with a column per product per purchase and be done with it. One thing you must guard against in IT is dogma. THOU SHALT DESIGN TO THIRD NORMAL FORM That's generally true, except when it isn't. As long as you recognize what you're giving up (repeat purchases, trending, single view of a customer, etc) then you can go with any design.

Otherwise, my design would look much like @MiNT. We have customers, products and an intersection between the two recording the customer's purchase activity. You wouldn't be adding rows to the Product table, that'd be duplicating data.

Huge assumption

It sounds to me like the shop is small enough that the customer base is known to the owners/operators and the answers to these questions don't matter. Who's their best customer? They'll tell you off the top of their head. This app is really there just to record the details of a quoted price. If that's so, you could use anything as your backend and the design won't really matter.

Improve this answer
1
  • Thank you for giving a practical example because this helps me understand the most. I do have to save detailed sales history, but even if I make a separate products table it won't help, because they deliver to the customers few times a week, the products info is to know how much to charge the customer (each customer has special price). After every purchase I save the details in a special 'Sales_History' table. About your assumption - it's half true. it is a small shop, but they do want many various reports (highest sale, total profit etc. etc.)
    – BornToCode
    Jul 23, 2012 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.