I am trying to design a relational database to manege two objects: residence and property.
a residence may have no property, one property, or many properties, and the property may not be related, but can only be related to a maximum of one residence (0:N relationship).
There are fields in common between the two tables: address, latitude, longitude and juridical_nature. In the case where a property is related to a residence, the value of the common fields must be identical to residence common fields value (same address, coordinates and juridical nature)
i am confused between two concepts
Concept 1
Each object table contains the common fields (address, latitude, longitude, juridical_nature). and i control the input by preventing the user to edit the property common fields and bringing the values from residence table
Residence:
residence_id|address|latitude|longitude|juridical_nature|residence_field6|residence_field7|residence_field8 ...
Property:
property_id|address|latitude|longitude|juridical_nature|property_Field6|property_Field7|property_Field8 ...
residence_property:
residence_id|property_id
Concept 2
Create a table for common fields and relate it to objects tables
Residence:
residence_id|common_fields_id|residence_field6|residence_field7|residence_field8 ...
Property:
property_id|common_fields_id|property_Field6|property_Field7|property_Field8 ...
residence_property:
residence_id|property_id
common_fields:
common_fields_id|address|latitude|longitude|juridical_nature
Which is the best and practical concept. and if there is another better concept would mind to share it with me
