April 9, 2011 at 9:48 am
Hello Experts,
I have two tables in my db broker and customer. They both have contact information. Fields for contact information are same. But I am not sure to design seprate tables for both contact information or should I place both contacts in single table.
For Example :-
Customer Table: - CutsomerId,CustomerName,CustomerCode,DateofBirth,
Broker Table: - BrokerId,BrokerCode, BrokerName, BrokerRank, Introducer etc....
For Contact
Case (1)
PersonContact :- AddressType(Permanent,Current), PersonType(1 for customer and 2 for broker),Address,City,State,PinCode,Phone etc...
Case (2)
CustomerContact :- AddressType,Address,City,State,PinCode,Phone
BrokerContact :- AddressType,Address,City,State,PinCode,Phone
Kindly suggest me which case is the better option
April 10, 2011 at 5:50 pm
Hi,
Based on the tables you already have, it seems to me that contacts should be two separate tables: one for customers and one for brokers.
Assuming that you out them all in one table and add field “PersonType”, would you hard-code it every time you are searching for a customer/broker contact? Since customer and broker are from two different tables they might have same ID, so your queries would need to have PersonType hard-coded to make sure that you are returning relevant contact
(something like: Clients.ClientId=contacts.ClientId and PersonType=1)
This is not a good practice.
Previous is based on the assumption that you already have tables Customers and Brokers and that it has been set up earlier.
In case you are designing them as well as the part of the same project, I would ask myself:
Are brokers customers as well?
Meaning: Is there any kind of a relationship between them?
Just guessing, but you might have situation like:
1.Table: Clients (general information)
2.Table: ClientType (customer, broker)
3.Table: Customers
4.Table: Brokers
In that case you could have: Table: ClientContacts that would cover both.
Please let me know in case you wanted me to provide more explanation for this second scenario – not easy to do it without better insight in the real world situation.
MB
April 11, 2011 at 3:09 am
Another way is, keep all addresses in one table. Add the Address Id in the Customer table and Broker table. In that case person type is not required in the address table.
In future if you may required to add Permanent address and Communication address separately. So you can add a separate address id in Customer table or Broker table. No need to create further address table, can keep those addresses also in the same address table without creating further address tables as we are adding address id as a foreign key in customer and broker tables.
Further, As MB said, all depends upon your scenario.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply