3

Here is my database design:

addressing people db 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?

enter image description here

0

2 Answers 2

2

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.

5
  • Thank you for your wisely comment. I edited to question. Please can you check my update? Best regards.
    – Halit D
    Mar 18, 2018 at 14:03
  • @HalitD You still not explained the need to break out city state country tables separately. I suspect you’ve still not thought through your business problem in extreme detail yet; you’ve certainly not described it here yet. Mar 18, 2018 at 16:38
  • I dont have any specific reason to keep them separately. I only thought preventing dublicate entry for these kind of information. Do you think that need to collect all of them on a table? Its also make sense.
    – Halit D
    Mar 18, 2018 at 16:44
  • I’ve already stated that usually a table with only one semantic field should not be a separate table at all. One possible justification is you are concerned about the name of the city, state, or country changing its name and you must update it. But usually that does not matter, as the postal system will continue to recognize the new name for longer than the likely lifespan of your app, or you can just do a one-time mass update on the database. Another justification might be to control data-entry (misspellings), but usually that is done in the app rather than the database. Mar 18, 2018 at 17:09
  • 1
    @HalitD I added a section at bottom about data-validation via a postal address verification service. Mar 18, 2018 at 20:20
1

I dislike normalizing each part of Locations. In some cases, I would have a Locations table with city, state, country each spelled out. In your case, I would simply spell them out in Office.

Also, I would use the standard abbreviations in

country_code CHAR(2) CHARACTER SET ascii

Normalization has two uses:

  • Allow for centralized changing when the spelling changes. But how often does a city/state/country change its name?

  • To save space. But will you be having millions of Offices?

You probably have lots of Persons in each Office, so that 'normalization' is justified -- for both reasons.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.