SQL Table design assitance

I'm building multiple web based tools for a business group. Many of these tools require a managed list of city names. Right now, each tool has its own look-up table of locations and I'd like to centralize it so one location table can be used for all tools, so a location doesn't need to be added multiple times.

I've created the master location table

-----------------------------
| LocationID | LocationName |
-----------------------------
|     1      |     Reno     |
-----------------------------
|     2      |  San Diego   |
-----------------------------

I'd like to add additional fields that are specific to each tool, but don't necessarily relate to one another. Should I create other tables to manage these fields, or just add the new fields to this location table as the need arises?

My initial thought is to create tables to hold the settings for each additional tool to reference.

WebTool1 settings table
    ------------------------------------------------------
    | LocationID | HasAirConditioning|  HasSecurityGuard |
    ------------------------------------------------------
    |     1      |     TRUE          |       TRUE        |
    ------------------------------------------------------
    |     2      |     FALSE         |      TRUE         |
    ------------------------------------------------------

WebTool2 settings table
    -------------------------------------------------------
    | LocationID | ServerName   |  RequiresDriveMapping   |
    -------------------------------------------------------
    |     1      | DELLSERVER1  |       TRUE              |
    -------------------------------------------------------
    |     2      |  HPSERVER3   |      FALSE              |
    -------------------------------------------------------

Is this a good strategy? If not, why?

Answers


I think thats a perfectly fine strategy.

With databases i tend to normalise first and ask questions later ... It is very rare that you run into performance problms ... and in this case the query syntax wont get more complicated.

The only problem is that you may want to construct constraints to ensure that the relations stay at 1:1. If it was a flat table this would not be an issue, but it would be harder to tell what fields belong to what tool.

The time when fields should appear in the base table is when they are common across all tools.

I personally like my database structure to reflect the business. It feels more self documenting imo.


Need Your Help

Flatten and map objects inner collection to another collection

c# automapper

I have a source enumeration which holds an inner enumeration, IEnumerable<>. I want to be able to take my sources inner enumeration and flatten it out into my destination enumeration. However,...

M3U playlists…why redundant files?

mp3 music shoutcast round-robin m3u

I'm taking a look at some random Icecast playlists (available here: http://dir.xiph.org/index.php) and I'm wondering why many seem to contain a list of the same mp3 file.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.