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.