CODEWITHSUNDEEP
mysql
sid
sid

Reputation: 61

what is the right way to design the table in a database?

i want to know what is the right way to do it .

We have area which can be northamerica, southamerica

we have goelocations which can be slc, murry, peru, mexico

so, one area can have many geolocations.

table for this is

ID      Area    Geolocation  
1       NA      slc  
2       NA      murry  
3       SA      peru  
4       SA      mexico

If number of area and geolocation are very limited and not expected to grow do you see a problem in this type of design vs table with FK like below

Area table

ID      Area  
1       NA  
2       SA

Geolocation table

ID    Geolocation     AreaID  
1       slc            1  
2       mexico         2  
3       peru           2

which one will be easy to query and use in a php application?

Upvotes: 1

Views: 68

Answers (4)

Marcus Adams
Marcus Adams

Reputation: 53850

This is denormalization and is fine. My rule for denormalization is, if I have a reason, it's enough of a reason. So, as long as you have a reason for such denormalization, then proceed.

The above design is not likely to make your queries more complicated. In fact, it might simplify them. It's just not optimized for asking questions like "What are the areas?"

Instead of this (normalized):

SELECT *
FROM areas

You have to do this (denormalized):

SELECT DISTINCT area
FROM geolocation

And what if there are areas that don't have a row in geolocation yet? How will you know about them?

Upvotes: 0

Ted Hopp
Ted Hopp

Reputation: 234807

Even though denormalization doesn't cost a lot because, as you say, the geolocation set is small and stable, it's still worth having a normalized table (as with your second option). You may decide to add (say) centralamerica or other regions. Or you may need to change the encoding used. The whole point of normalization is to make it easier to deal with changes you did not expect, not just the ones you can foresee.

The performance penalty for lookups is very small (if there is any at all). Updates will be easier and the data base will probably be smaller with an FK-based schema.

Upvotes: 1

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

It depends on your requirements, if you want more flexibility you could have two lookup tables (Area and Geolocation) and a 'mapping' table where you create different configurations of area/geolocation.

That would give you more flexibility to do different mappings without affecting existing data, though looking at the data you probably don't require it in this case.

Upvotes: 1

duffymo
duffymo

Reputation: 308763

Sounds like a simple one-to-many relationship between the AREA and GEOLOCATION tables. Any relational database could handle such a thing. Add a foreign key to GEOLOCATION that points back to the AREA primary key and you're in business. Don't forget to add an index to the GEOLOCATION foreign key column.

Upvotes: 0

Related Questions