Brainwash Design Table 1

[peac3]

Hi Guys,

I just would like to get the input from your experience about architectural design table.

At the moment, we are getting the data from many different source system and those data consolidated into one big table.

then we have new source system coming with new informations and it requires adding new column into our consolidation table.

But we were thinking that it's abit of waste to add new columns just for the sake of this one source system because most of new columns's value would be NULL.

So what is you guys experience about this... and how to handle this situation.

Any input would be appreciated.

Thanks in advance.

 

[gmmastros]

For me, it would depend on the size of your table. If the table was relatively small, I would just add the additional columns because it's a bit easier to deal with.

If the table has millions of rows, I would create a second table that only gets populated when there is data for it. So, out of millions of rows, there may only be a couple thousand rows in the other table. This second table should have a primary key that matches the original table's primary key, and you should define it a a foreign key to the original table. I would even go so far as setting up cascade deletes for it.

Then, the only time you need to touch that table is when you want the additional data from it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MegaSQLBI]

George you are a master Nice tip!

Thanks
Mega

 

[peac3]

Thanks George, I would like to discuss more if that's ok...

Yes, the table contains tens of millions of records, I already thought about matching the primary key with the original table.

And about your statement

you should define it a a foreign key to the original table.

In the original table the columns look like this (ACCT_ID | DEBT_A | CREDIT_A | UNDRAWN_BALANCE, etc ...)
and from new source system data or second table that would be new columns like ( INSURANCE_BALANCE | INSURANCE_REPAYMENT, etc)

the natural/primary key would be ACCT_ID and if we would like to know which source system its coming from we need to map the ACCT_ID with the other lookup table which columns like (ACCT_ID, SOURCE_SYSTEM, FACILITY_NUMBER, etc...)

Then how do we define the foreign key for the second table?

I would even go so far as setting up cascade deletes for it.
Then, the only time you need to touch that table is when you want the additional data from it.

Can you please explain more details about this?

Thanks heaps,