4

I am about to embark on a redesign of an application, one where querying the database is particularly annoying. I intend to redesign the database as much as possible but the data shape cannot change too much.

The database has a main table and 10 other tables each representing a record type. The main table has all of the data the record types have in common. Record ID, DateLogged etc. Around 30-40 columns in total. The other tables are all completely different as each record type is very different from another. They all have about 20-30 columns each.

The main table has a column called type, which is an int. However nowhere does it reference what record types the type numbers refer to. You can just figure it our by looking at the procedures. The main table has to join with each type table to allow searching. I have added an image representing what I am trying to describe.

enter image description here

Is there a better way to create these relationships? I would like to ditch the verbose stored procs used with ADO.NET and move to EF. I need to think of a better way to relating the data so people in the future wont need to work out the relationships by scouring stored procedures for clues.

Improve this question
4
  • 1
    If you ignore the non-descriptive table names and lack of a type-lookup, the current design is a good way to model non-dynamic sub-types in an RDBMS. Does it give Entity Framework hiccups?
    – mike30
    Jun 5, 2013 at 20:52
  • I haven't even got that far yet, I was only just given the project. It's a DB i've queried a lot over the last 2 years so was thinking how to make it better. The ADO.NET queries are huge so mapping entities will clean up a lot of the code. I was just thinking through ideas.
    – Lotok
    Jun 5, 2013 at 20:56
  • @James: are those TypeX records in your example associated mutually exclusive to a Main record, dependend on the "Type" field?
    – Doc Brown
    Jun 6, 2013 at 16:30
  • @DocBrown Each record in main will also need to join to just one of the typeX records.
    – Lotok
    Jun 6, 2013 at 17:06

1 Answer 1

Reset to default
5

Add a Type table.

TypeID     PK
Type       string

Change Type in Main to TypeID, making it a Foreign Key to the Type table TypeID.

Where you go from here depends on how distinct each type is. If the only distinction is that the fields vary somewhat between types, and there are only a few types, you might get away with having a single Types table, adding a TypeID to it, and putting every field for every type in each record (with the understanding that some fields are going to be empty for every record).

If the types are very distinct, you can keep your current design, but add TypeID to each of the Type tables.

Improve this answer
5
  • I had considered denormalising it but the main table would be huge. I think your suggestion is probably the only way, they are very distinct so keeping it as 11 tables is probably inevitable. The additional lookup table to give a name to the Id will at least make it more readable for people needing to query it in the future
    – Lotok
    Jun 5, 2013 at 20:38
  • @James How huge is 'huge'? Based on your example you might only need a few columns: Date1, Date2, String1, String2, Int1, Int2. In your documentation you can describe that for Type1 Date1 => CloseDate and or Type2 String1 => SiteName. etc. That way you don't need to leave an empty string field because the names are different between types.
    – Brad
    Jun 5, 2013 at 20:53
  • Yeah my example was short for examples sake. Each type table has roughly 30 distinct columns. So about 300 columns to add on to the main table columns If i denormalised.
    – Lotok
    Jun 5, 2013 at 20:57
  • @James: do yourself a favor and don't denormalise if you don't want to run into problems with redundant data. If you want to avoid JOINs to make ad-hoc queries simpler, add some additional views, which encapsulate the most often needed JOINs.
    – Doc Brown
    Jun 6, 2013 at 16:35
  • @DocBrown yeah i decided against denormalising, used Roberts suggestion. I have now changed some of the app over to EF and started the process of replacing the stored procedures and ADO.NET calls. So far so good.
    – Lotok
    Jun 6, 2013 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.