CODEWITHSUNDEEP
databasesql-server-2005entity-frameworkdatabase-design
Rachel
Rachel

Reputation: 132548

Should I use and add on to the existing denormalized DB structure, or create my own?

We have a 3rd party software, and I have been asked to create an extension for it. The extension will deal with data that exists in the current software, and include some additional data.

The existing data is extremely denormalized. Most of the data I will be working with, which is for about 4-5 different entities, exists in a single table. I will need to add additional fields to most of these entities.

I see 2 ways of doing this:

  1. Create a new table for the new fields, that matches the existing table 1:1 and uses the same denormalized style they use

    Pros: It would fit in with the existing denormalized database structure, and be easy to create

    Cons: Very denormalized, and I'm a bit obsessed about normalizing my databases. The current table already has over 50 columns, and it should really be split up into 4-5 different tables.

    I also don't think I can use EF easily with the current database structure (my preferred way of working with the database) and would have to manually create data models in the code instead of using EF to generate them

  2. Create a whole new set of tables and use triggers to synchronize the data between the tables

    Pros: Can create the tables the way I want, and can use EF to handle database operations, and to generate the data models for me

    Cons: Need to use Triggers to synchronize data with existing table

Which do you think is the better idea?

Upvotes: 0

Views: 65

Answers (2)

Joel Brown
Joel Brown

Reputation: 14388

I think it depends on how much of an extension you're looking at. If you are adding a couple of bells and whistles to the existing interface, and especially if you aren't going to have to wrap the existing interface, then you might be better off to hold your nose and live with the ugly base data structure.

It all comes down to which is a smaller pile of code to build and maintain:

  1. Extension code that is made ugly by the denormalized base + extended attributes - or -

  2. Clean extension code + all of the ugly table synchronization trigger code.

It seems to me that if the extension is really substantial, then 2 could turn out to be smaller than 1. However, if you're looking at just those few bells and whistles, then 1 is probably going to be a lot smaller than 2.

Upvotes: 1

Jeff Willener
Jeff Willener

Reputation: 749

I think the answer is how to best utilize your time combined with how much control you have over the tables for now and in the future. Personally I would create an alternate table with some relationship. If you choose to modify the existing table you not only now have to regression test the app and test your new app but you stand the risk of future updates breaking both apps. So I say, work with what you can control. You're time will be more effectively used and you position yourself with less risk down the road. Smart for you and smart for business. Hope that helps.

Upvotes: 1

Related Questions