[lmei007]

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.

[r937]

1. not
2. custId should be PK

[lmei007]

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,

[lmei007]

Originally Posted by r937

2. custId should be PK

Should I specify the custId is a foreign key also?

[r937]

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

[r937]

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