Splitting Table

[Bridget2]

Hi,

I have been given the task of creating an enterprise wide product file. We currently have 3 applications with their own product file, all of which are different

It is a large file with one application having 300 fields!

As part of my project I was thinking of splitting the file into maybe a dozen smaller tables and amalgamating the 3 current files, with the product number as the primary key on each of the new tables.

Is this the best way to go about this? Are there any rules governing the number of tables I should create or is there even a better way of doing this?

You may have gathered I am very new at this

I'd appreciate any help.

Thanks
Bridget

 

[TomBascom]

Are you familiar with the concept of normalizing your schema? 3rd normal form is generally a good starting point and fairly easy to understand. 4th & 5th normal forms are less common...

3rd normal form essentially says that every field in the table is related to the key, the whole key and nothing but the key.

 

[Bridget2]

Thanks Tom. I'm sort of au fait with normalization. What do you think would be the right way to proceed? I don't think it appropriate to design the new table with 300 fields. I was thinking of buildingthe new product table containing only 6 or so fields with the other 290 or so broken out into about 8 tables. There are about 8 logical groups of fields in this table.

Thanks again. I appreciate your help.

 

[TomBascom]

Of course I don't know the gory details of your situation but, on the face of it, that seems reasonable. I often take that approach when strict 3rd normal form would require one table but with a huge number of fields. If there are some clear logical groupings it helps me to rationalize the decision :awink:

I'd still start by getting the 3nf design right first -- then break it up if necessary. A lot of times a close look at those 300 fields reveals that there aren't really 300 fields. Take, for example a mortgage loan. At 1st blush you might think that a "loanfile" table needs to have dozens of fields (hundreds actually if you get into all the ways that fees get split between parties...) for all of the various fees that get collected. But that turns into a (related) table of fees with a fee type. Suddenly dozens, or hundreds, of fields becomes just a few but there are many records per loan.