Normalization for three somehow identical tables

database-designnormalizationsubtypes

These three tables are nearly identical. But, there are some exceptions.

But, I can't find best normalized structure. Could you please help me?

Here is the informations must be hold in database:

 1) APARTMENTS
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      APARTMENT
      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

 2) GARDENES
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING

      LIVING_SPACE
      ELECTRIC_SUBSCRIBER_NUMBER
      GAS_SUBSCRIBER_NUMBER
      WATER_SUBSCRIBER_NUMBER

      TOTAL_GARDEN_SPACE

 3) PLOT_OF_LAND
      CITY_ID
      VILLAGE_ID
      STREET_NAME
      BUILDING
      LAND_SPACE

What is the best structure for this tables?

Yet, I have made two structures:

FIRST ONE:

enter image description here

But, it seems not the best to me. So, i have made second choice:
SECOND ONE:
May be I must make on table with the name POSSESSİONS? And inside this:

POSSESSIONS
   ID
   TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)
   SUBSCRIBER_ID
   ADDRESS_ID

   LIVING_SPACE
   TOTAL_GARDEN_SPACE
   LAND_SPACE

What is your recomendations?

Best Answer

The question one has to ask is: Will I ever need the address id and the subscriber id more than once? If the answere to this is yes, go with the second approach. If the answere is no you could still use the second approach, but most of the time it is more convenient to use the first one.

If the triple ELECTRIC_SUBSCRIBER_NUMBER, GAS_SUBSCRIBER_NUMBER, WATER_SUBSCRIBER_NUMBER will always be the same for an owner, then I would make a separate table out of it, because it may happen, that one owner has multiple possessions.

Since the address of a posession and the space does uniquily belong to one possession there is no reason to split it out.

My approach would look like this:

POSSESSIONS
  ID
  [TYPE (1-APARTMENT, 2-GARDEN, 3-PLOT_OF_LAND)]
  CITY_ID
  VILLAGE_ID
  STREET_NAME
  BUILDING
  APARTMENT (nullable)
  SPACE
  SUBSCRIBER_ID (nullable)

SUBSCRIBERS
  ID
  ELECTRIC_SUBSCRIBER_NUMBER
  GAS_SUBSCRIBER_NUMBER
  WATER_SUBSCRIBER_NUMBER

You may skip the TYPE field, because the information is already encoded in the Nulls of APARTMENT and SUBSCRIBER_ID.

Normalization is all about eliminating redundacies. A good approach to work this out is, to take your scheme with good sample data and then try to change some information by hand. If you have to change data in multiple places for one bit of "atomic information", you should probabbly rethink your scheme. If you need to think about where this information was, because you have multiple tables with similar information schemes, then again, you should probabbly rethink your scheme.

Related Question