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.
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.
4
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.
5