Help determining Maintenance Item table structure best practice

Question

I have two different existing Database design's in the legacy apps I inherited. On one side they created two tables; tblAdminMaintCategory and tblAdminMaintItems. On the other side individual tables for everything; tblAdminPersonTitle and tblAdminRace.

Method #1

Now in the first example there would be an entry in tblAdminMaintCategory for Race(say ID is #2) and then in tblAdminMaintItems each individual race would have an entry with the corressponding categoryID. Then to query it for race options, for example, would go --> SELECT * FROM tblAdminMaintItems WHERE CategoryID = 2

Method #2

Now in the second example there would just be an entry in tblAdminRace for each individual race option. To query that would go --> SELECT * FROM tblAdminRace.

Now, I am trying to figure out, going forward, which of these paths I want to follow in my own apps. I don't like that the First Method, seemingly, introduces magic numbers. I don't like that the Second Method introduces many, many, small tables but I am not sure that is the END OF THE WORLD!!

I am curious as to others opinions on how they would proceed or how they have proceeded. What worked for you? What are some reasons I shouldn't use one or the other?

Thanks!

Answer 1

Magic numbers always seem like a bad idee. Specifically where you can use more desciptive codes in tables that do not have that many entries (Titles, Races, etc).

On the other hand having a gazillion small tables to link to not only makes the queries difficult to maintain, but harder to read and more joins to parse.

EDIT: Smaller reference tables will make maintinance easier. Change in one place change all. But it will definately cluter up you table structure designers, and forgetting to populate these metadata tables will give you a lot of issues at new install sites.

Answer 2

It is reasonable design to have separate entities in different tables; like: Race, Car, Person, Location, Maintenance task, Maintenance schedule. If you don't like joins in queries, simply design several views. I may have misunderstood the example, but here is one suggestion.