how to store regions in a database field

Question

i have a webbased tracking application and i am storing data in SQL server. I am tracking which locations i have deployed applications. The web interface is a dropdown combo and I have a varchar(100) field right now. I have the user select a dropdown list:

• Global
• America
• Europe
• Asia

but now i have applications that are stored in more than one region (but not necessarily global)

should i get rid of the "global" option and just have a multi-select field with America, Europe and Asia.

I am trying to think of the implications when i go query this data as i would like to slice and dice this data and run queries to get metrics per region, etc.

what is the best way for me to store this data?

Answer 1

If your data in real life is many-to-many then I would recommend having a locations table, an applications table and a join table. There is no reason that this would stop you from running any kind of query that you want on the data.

Answer 2

If your number of regions is small (you only have three here), you could use a tinyint to store the values:

America = 128, Europe = 64, Asia = 32

If you're using C#, you could also set up an enumeration to store those values

[Flags]
public enum Region: byte
{
  America = 128,
  Europe = 64,
  Asia = 32,
  Global = 224
}

And do bitwise operations against them:

bool isInAmerica = (myValue & Region.America) == Region.America;

I think you can also do bitwise operations in SQL Server.

Answer 3

You don't say which version of SQL Server. Have you considered mapping your broad regions into actual geographical ones? That way you can use the spatial features of SQL Server 2008 and if you add more detail on the user later defining locations, your database and queries will work even better.

Answer 4

Sounds like a standard many-to-many thing. Like:

+-------+   +-----------------+   +--------+
| App   |   | App_Region      |   | Region |
+-------+   +-----------------+   +--------+
| id PK |<--| app_id PK FK    | ->| id PK  |
| name  |   | region_id PK FK |/  | name   |
+-------+   +-----------------+   +--------+

The App_Region there serving as the link table, so you can link each application to multiple regions.

Answer 5

I'd consider creating a region table to hold Global, America, Europe, Asia (and any future regions), then create a region_application so you can link applications to multiple regions. Since everything will be index-based, your queries should perform well and you will be able to easily slice them by region.

Answer 6

I will not get into details of the UI, but when it comes to slicing and dicing, this should help.