I want to find out how i go about storing codes in a database. The codes are going to reference information about a specific entity i.e title code, status code. I want to use these codes to populate dropdowns with their respective information. I wanna know if I should store them in the same table rather than in different tables or the other way around.
Storing them in the same table sounds like the easiest way to do it, but these codes could be complex entities themselves. I want this information to be reusable in any software that i might be working on in future. So i must be able to store any type of code.
I'd store each in their own table based on how many and how complex they are. I prefer seperate tables though, just a personal "clean,clear" thing
.
In the scenario i work in, we use both. complex codes = their own table , simple code = in one table.
Complex code would be one with say an "Effective Date" , so it's only applicable for a specific period, or one with alot of meta data.
To store them in one table is "easy" from a design side, but it can be frustratingly difficult to actually get it out / use it later on.
i.e.
You'd have :
TABLE:tTitleCodes ( tID , TitleCode , TitleCodeName , [anything else] )
TABLE:tStatusCodes ( sID , StatusCode, StatusCodeName , [anything else] )
vs.
TABLE : tCodes ( cID, CodeTYPE , CodeValue , CodeName [anything else] )
So to populate the Status Code dropdown you will have:
select StatusCode,StatusCodeName from tStatus
vs.
select CodeValue,CodeName from Codes where codeType = 'Status'
which is still ok, but now you want to store this info relative to other data in a table which will have columns like ( Date, UserID, StatusCodeID , TitleCodeID).
And then it gets tricky to translate that back to the single table. Now you start double joining to the same table and whatnot...
Obviously seperate tables imho, is alot easier to work with in the long run...especially when it comes to data warehousing and those things.
Sign up with Google