Database Normalization design Issue: 2 tables sharing almost the same information

Question

Example Scenario: Let say I only have 2 types of buyers for a product house.

Buyer 1: Individual Buyer

Buyer 2: Company Buyer

Distinct for Buyer 1 attributes: FName, LName, Bdate and Age

Distinct for Buyer 2 attributes: Company_Name, Nature_Of_Business and Type_Of_Business

Common for the 2 Buyers are: Address, Email, Tel_No, Country

 Db Table Name: Buyer
 Attributes: BuyerID, BuyerType **FName,LName,Bdate,Age**,Company_Name, Nature_Of_Business and Type_Of_Business

My Explanation: If you try to see the table Buyer, both the attributes of Individual and Company buyer are merged because they are all buyers they are just categorized base on BuyerType(Individual or Company)

Issue: If the buyer type is a Company then my Individual Attributes(i.e.FName,LName etc.) will be recorded empty or vice versa. My thoughts prevents me to separate them because I DON'T want to create Buyer's ID for each table.They should have only 1 BuyerID whether they are Individual or a Company.

Problem: How to construct DB Table(s) to solve this query:

I want report that shows all the buyers information with no empty records.

Sounds crazy but when the possible report will be generated, the details might give empty records of the Individual buyer fields if the buyer is type Company

Note: This can be done easily if I will filter specific Buyer type but that is not the case. I want all.

Sorry, but I don't quite understand this: `I DON'T want to create Buyer's ID for each table`. Can a buyer be individual and company or should a buyer only be one of them? Probably this [answer](http://stackoverflow.com/questions/8685621/what-is-the-best-database-schema-to-support-values-that-are-only-appropriate-to/9460541#9460541) can help you. — Mosty Mostacho, Nov 29 '12 at 15:01
@MostyMostacho It means that by creating 2 separate tables for Individual Buyer and Company Buyer, that will result to duplication of Buyer's ID because they are all buyers. Thanks — Raf, Nov 29 '12 at 15:17
They won't be duplicated as they will be different buyers and it is perfectly ok for them to be different. You didn't answer the question I did, though :) — Mosty Mostacho, Nov 29 '12 at 15:19
@MostyMostacho sorry my english is not that good. So you mean its ok to construct 2 table buyers (Individual & Company)? If so, the closest possibility will be Individual buyer has ID #1 and Company buyer will have also ID #1. How do you reconcile that on report? — Raf, Nov 29 '12 at 15:35
Hmm, I don't understand why they should be reconciled. Should they be liked together? If that is the case then I would create an "Abstract Buyer" table and then one for each of the other ones. Of course, each table will have its own ID and will be different. What you call `reconciliation` is just a foreign key that will link each children table to the parent table. Just a simple `inner join` will be enough :) Check the link I provided I've clearly explained how to do this. — Mosty Mostacho, Nov 29 '12 at 18:14

Gilbert Le Blanc · Accepted Answer · 2013-01-18T13:40:57.460

2

You can calculate age from birth date, so there's no need to store the age.

You have a buyer table and separate individual buyer and company buyer tables.

Buyer
------
Buyer ID
Buyer Type
Buyer Type ID
Address
Country
Email
Telephone Number

Individual Buyer
----------------
Individual Buyer ID
Last Name
First Name
Birth Date

Company Buyer
-------------
Company Buyer ID
Company Name
Type of Business
Nature of Business

Buyer Type is an indicator that points to the particular sub table for this buyer. 'I' for individual and 'C' for company would be one way to define the indicator.

Buyer Type ID is the foreign key to either Individual Buyer ID or Company Buyer ID.

The SQL to get all the buyer information would be

SELECT *
FROM Buyer, "Individual Buyer", "Company Buyer"
WHERE "Buyer ID" = 12345
  AND (("Buyer Type ID" = "Individual Buyer ID") AND ("Buyer Type" = 'I'))
   OR (("Buyer Type ID" = "Company Buyer ID") AND ("Buyer Type" = 'C'))

If you want more than one Buyer row, adjust the WHERE clause.

edited Jan 18 '13 at 13:40

answered Nov 29 '12 at 15:04

Gilbert Le Blanc

50,182
6
67
111

Thank you. I'll consider this as a solution but will experiment this with sample data so I can test it thoroughly. I really appreciate it. My concern is during generation of reports. – Raf Nov 29 '12 at 15:38
I Think I miss to ask about the Joining of tables. How does this works when I want to query the Buyer table that will list down the buyer records both from 'Individual' and 'Company' with their corresponding individual information? because when I basically join the tables I can only get the IDs from 'Individual' and 'Company' but find hard to get First_Name and Last_Name from Individual table and Company_Name and Nature_Of_Business. I'm doing now the reporting but took already 2 days to analyze and get back to you to maybe you can still answer my reporting issue. – Raf Jan 18 '13 at 13:34
Thanks a lot. I really appreciate your help. Very well initiated. I successfully get what I need BUT though there is no error I got a warning: "unknown table status: TABLE_TYPE". Not really as necessary but might let me know if you know what this mean. Thank you very much Sir Gilbert. – Raf Jan 18 '13 at 13:57
1

oh sorry,. I just found it out. Need to put SELECT Buyer.*, Individual_Buyer.*, Company_Buyer.* and NOT just SELECT * to avoid the warnings. – Raf Jan 18 '13 at 14:04

Vincent Malgrat · Answer 2 · 2012-11-29T16:49:35.877

Your logical schema will probably have three distinct entities: an abstract Buyer that contains all common fields, and two subentities that inherit from it: Individual Buyer and Company Buyer.

How you implement that schema physically is up to you. Usually, all logical entities that share the same primary key (here buyerID) will be merged in the same physical table.

Having a single table makes sense:

from a performance point of view: filtering takes less resources than joining generally. DML will also be a lot faster with a single table.
from an integrity point of view: it is very easy to insert invalid data when you have multiple tables. For instance, it is hard to guarantee that a buyerID has at least one and at most one row in the subentities if you have three tables.

I would go for a single physical table with constraints:

CREATE TABLE buyer (BuyerID primary key, BuyerType, 
                    FName,LName,Bdate,
                    Company_Name, Nature_Of_Business, Type_Of_Business,
                    CONSTRAINT individual_chk 
                       CHECK (BuyerType = 2 OR (Company_name IS NULL AND
                                                Nature_Of_Business IS NULL AND
                                                Type_Of_Business IS NULL)
                              ),
                    CONSTRAINT company_chk
                       CHECK (BuyerType = 1 OR (...))
                   )

The check constraints would also validate that the required fields are not null for each type.

You can then create views if you need access to individual and business separately:

CREATE VIEW individual_buyer IS 
   SELECT BuyerID, 
          FName,LName,Bdate
     FROM buyer 
    WHERE buyerType = 1

CREATE VIEW company_buyer IS 
   SELECT BuyerID, 
          Company_Name, Nature_Of_Business, Type_Of_Business
     FROM buyer 
    WHERE buyerType = 2

Database Normalization design Issue: 2 tables sharing almost the same information

2 Answers2