Crazy Horse
asked on
Whether to store ID's or text values for detailed information
I have a line item that has a category and location. The way I store those in the database is by using the ID.
So, I have a separate table for categories and and a separate table for locations. When I add a new row into the third table, it stores the ID values for category and location e.g.:
Name Category Location
--------- --------- ---------
Joe Soap 5 13
This seems like a good way to do things until I need to display the data.
In order to actually show a category name and location name, I would have to do table joins to check what the actual category name is for ID 5 and what the actual location name is for ID 13.
This seems like a lot of work just to extract the names. But maybe there is an easier way?
Would it be okay to store the text values in the database like this or is it incorrect to do so? It would just make life considerably easier and quicker than having to look up the ID's from 2 different tables.
Name Category Location
--------- --------- ---------
Joe Soap Sport Timbuktu
So, I have a separate table for categories and and a separate table for locations. When I add a new row into the third table, it stores the ID values for category and location e.g.:
Name Category Location
--------- --------- ---------
Joe Soap 5 13
This seems like a good way to do things until I need to display the data.
In order to actually show a category name and location name, I would have to do table joins to check what the actual category name is for ID 5 and what the actual location name is for ID 13.
This seems like a lot of work just to extract the names. But maybe there is an easier way?
Would it be okay to store the text values in the database like this or is it incorrect to do so? It would just make life considerably easier and quicker than having to look up the ID's from 2 different tables.
Name Category Location
--------- --------- ---------
Joe Soap Sport Timbuktu
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another point I forgot to mention - if your Categories are stored in their own table, then it actually makes displaying data somewhat easier. You can just query the Category table to get a list of Categories. You can query your Items table to get all items that belong to a given Category.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.