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开发者_如何学Go 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?

---

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.

---

If you have a large number of those extra fields, it would make sense to keep them divided as a kind of natural partitioning. Otherwise, I don't think it really matters one way or the other as far as the database is concerned.

Breaking the fields out by some intrinsic logical grouping instead of application might also make more sense from a pure schema perspective. For instance LocationItInfrastructure and LocationBuildingOperations or somesuch rather than LocationApp1 and LocaationApp2.

Overall, I think whichever approach personally makes the most sense to you and is easiest for you to work with will be the best solution.

---

How big those tables are going to be? If the tables will contain 1000 or less rows, no problem. But if it is 100K or more, you need to consider how you are going to query those tables because table joins can become slow.

For example you want to show all rows with SERVERNAME = HPSERVER3 and LOCATIONNAME = RENO plus your transaction tables, if HPSERVER3 has lots of rows and RENO has lots of rows too, it is possible the query planner will perform nested join and the query will be slow. Index will help but it is trickier to set up compared to combining all columns into a single table.