Click here to Skip to main content
15,037,420 members
Please Sign up or sign in to vote.
2.00/5 (3 votes)
See more:
Hi fellows hope so all are doing good :)
      I am developing a Sale/Purchase system for a local store where data may varies from thousands to about a million of records. I need two suggestion for database design.


  1. Question 1)

    • In my case there are two types of Clients "Individual and Company".
    • Company can have more than one Representatives and have more properties(Columns) than Individuals
    • So I creates two tables in parent child combination. Clients with some properties and Companies with extended properties
    • A Bill will be generate against a Client
    • Every entry of type Company, Individual and Representative will go in Clients but only Company type Client will be inserted in Companies(Extended) table
    • Companies have 5 or 6 additional columns except Client_ID(FK from Clients table).
    • I put companies and individual clients together because there are some common columns like balance, credit limit, member package, account info(There/His bank accounts we are dealing with) e.t.c

    So what if I merge both tables. Would saving 6 bytes per record is better or eliminating a table or join.
  2. Question 2)

    • I have to manage balances in two tables 1. In Clients 2. In Accounts
    • And for sure each table have a column named Balance with type money
    • Account table holds information about Bank/Cash in hand accounts of user not the balance of a client. Client's balance is in Balance column in Clients table for which I am asking to split or not.
    • I heard that frequent update of a record(column/like Balance in this case) effect the performance of queries even of select
    • And I heard it too to keeping frequently changing columns in smaller table other than larger table can give better performance I don't know is this write or not :(
    • So if the above statement is true than what if I create a table of Balances with auto incremental ID and make it's reference to both Clients and Accounts tables. One to One relation
    • I think it will increase the update performance for Balance but will increase an inner join for SELECT



    1. Thanks in advance and I am sorry if a have make any blunder :|
Posted
Updated 21-Dec-12 12:06pm
v2
Comments
Abhishek Pant 20-Dec-12 10:34am
   
BC @ CV 20-Dec-12 12:25pm
   
Excellent blog post. This looking for others to do the work for them without even trying to do it themselves is a phenomena that goes well beyond programming. It sadly seems to be a generation defining characteristic.
fahadrk 21-Dec-12 19:24pm
   
Check question again

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900