Welcome Guest, Not a member yet? Register   Sign In
Database Design
#1

[eluser]jleequeen[/eluser]
Hi all,

In rewriting my app in CI, I have stumbled upon a question related to the database. I currently have a table of counties. Right now I have a counties ID which is 1-100 for each county. Then where needed elsewhere in my tables, I use this County ID. Of course when I do any queries using any of the county information, I have to do a JOIN to get the actual county name. I'm wonder if it would be better to actually have the county name as the "Natural Key" instead of just a numerical ID.

The name of the counties will never change, and it seems like it'd make the queries much better and dumps of the data easier if it just had the full county name without having to do the JOIN.

Anyone have two cents to throw my way on this?
#2

[eluser]gtech[/eluser]
Here is my two cents.

Apparently numerical IDs are more efficient to search by. But if in another table (lets call it table2) only the county name is associated with the data, then I guess it makes sense to store the country name as text. if you ever require to search in table2 by country then it make sense to use the ID. If you are using more than the country name to associate with table2 (for example population) then maybe using the IDs also makes more sense.

The disadvantages of your approach..
1) if you decide to add more information to the country table, you wont be able to join it with the search results from table2 without having an inefficient search.
2) you are potentially using more table space to store data (even though its negligible).
3) you need to think what might happen if data gets deleted from the countries table, and whether being left with rouge country names matters.
4) if you are searching in table2 by the country name (as opposed to the ID) your search will potentially be less efficient.

Advantages
1) you don't need to use a join to get the data you require.

its up to you to make the call, in my opinion you just need to think whether requirements may change within your webapp.




Theme © iAndrew 2016 - Forum software by © MyBB