Sql-server – DB design for the project (addressing people)

database-designMySQLsql server

Here is my database design:

My first goal is easily grouping people and their own devices (this project will be for asset management) by country, city, office or state.

Second goal is preventing duplicate entries for the same office. Once an office is created with dependency information, other people need to be added with existing information of certain office.

What do you think about my design? Ideal or more complex than required?

edit:

Thank you for your wisely comment. If I change my structure like below, can an user add a city for multiple countries. Isn't it? Can add London city for England and France. As well as state or address. Sholud I prevent this condition with filtering to populate listbox with query?

Best Answer

Vague requirements

Your requirements are too vague. Add more detail. Talk about how each entity relates to another in very precise terms.

Address

The address should be assigned to the office. If the office can have only one address, then address should likely be part of the office table.

The hierarchy of country-state-city makes no sense to me. Explain exactly how your requirements demand that structure. Reporting on such values is done by queries, with sorting and grouping, not building out separate tables. Generally, a related table with only a single semantic column should not be a separate table.

You talked about assigning people to offices but your design makes no such association.

Data-Validation

Some of your concerns seem to be related to validating the data-entry of the address. Usually the app is the front-line of defense for that kind of data-validation, with checks on the database side playing a secondary role.

Either way (app or db), your attempts shown here are meager, and insufficient to ensure valid data. If you are going to do data-validation on the address, do it seriously: Rather than hack your own checks, leverage a postal address verification system driven by postal-code. Either use a remote service or provide your own locally with complete dataset of known addresses and an existing library for look-up and comparing of address info. Your business-problem data should not be directly linked to such tables, but rather your app or database trigger should call out to the remote or local address-verification service.

Naming issues

Change People to singular Person.

Fix spelling of Adress to Address.

Use all lowercase in the names for maximum portability and avoid problems.

Fix Name appearing twice on the address table.

Related Solutions

Postgresql – How to design a database for continents, countries, regions, cities and POIs

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J

How to link a customer to a country when the city/state isn’t known

Use a variation of number 2 - a location table with a involuted (self-referencing)relationship, but add something to make the hierarchy management easier. For example, use nested sets/visitation numbers (see here) or an adjacency list.

The advantages of this approach are that:

You have a single foreign key from your transaction to your location table. This keeps your queries simple insofar as you don't need multiple joins and either/or logic.
The foreign key can point to as vague or as specific a location as you need.
You can easily traverse the hierarchy (since you'd be using a technique like nested sets) to get the level of geography of interest.
You can accommodate countries that have more or less than one geographical division between the country and the city, without having to create dummy entries or duplicating data.

I don't know what your ORM is, but it should be able to handle a self-referencing relationship and a partitioning attribute (i.e. GeographyLevel = { country, state, city })