Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    split a big table to few smaller tables

    I have a customer table which has lots of infomation (bank, expense, income, address, properties, ....). It is too big in my view. So I decided to split it into few smaller tables: customer, expense, income, address.

    1. my decision is good or not?
    2. the customer table has a custId as its primary key. For all other smaller tables, I can use the custId as its primary key, or I can declare a different primary key for those smaller tables and use the custId as their foreign key. Which way is better?

    thank you guys.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. not
    2. custId should be PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    20
    Quote Originally Posted by r937
    1. not
    Why is not good? because there will be join queries? That means the size of a table does not matter as long as those columns are needed?

    Does that also mean we should join all tables together if those tables only have a simple one-to-one relationship?

    thanks,

  4. #4
    Join Date
    Jun 2008
    Posts
    20
    Quote Originally Posted by r937
    2. custId should be PK
    Should I specify the custId is a foreign key also?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lmei007
    Why is not good?
    because breaking a table up because it's "too big" is not the same as breaking a table up because it isn't in a high enough normal form

    normalize first, then run your app for a year or two, and then break it up (i.e. partition) for performance if necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lmei007
    Should I specify the custId is a foreign key also?
    in a 1-to-1 relationship, yes, one of the tables will have a PK, and the other will have the same column as a PK/FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •