I have a database that is for managing rental properties

It has a property table that has details about the property such as bedrooms, sq feet, price, etc, it also has a column called address_id which is a foreign key to the address table which contains the address like street name, city, etc

Is there any benefit of having the address in another table? Or can I move the address columns to the property table?

  • All depends on what sort of queries you run. Write the queries for both forms of the table and make a decision based on which is simpler. If in doubt, show the queries. – danblack May 3 '19 at 5:09

It depends on whether the relationship between property and address is 1:1.

Put another way: it depends whether two properties may reside at one address, or one property may have two addresses.

I can imagine, for example, a large worksite with multiple buildings. The address for the worksite might be 50 Smith St (one address). And I can imagine your property table having two rows linking to that address: one where property.buildingName = 'Workshed' and another where property.buildingName = 'Reception' (although I'm not sure you would rent a workshed separately to a reception, and if you did, they would probably be given separate addresses anyway).

If the above modeling of two properties at one address is possible with your business data, then I advise against merging your two tables.

On the other hand, if there is only ever 1 property per address, I think there is no real issue with merging your tables.

Other considerations:

  • If your database was managing millions of rental properties, there may be performance differences between the two designs. This would be an avenue to explore further.

  • If your application currently references two separate tables, there will be work converting your application to reference just the single table. This work might be trivial (if, say, currently, all references query a view and you only have to update your view) or monumental (if, say, you have 200 reports querying these tables and your data is synchronised with 5 other systems whereby you have to update your sync process and perform regression testing across all of those systems, even if they all belong to business clients). The prospective cost of the work to merge tables should also be taken into account.

  • If this is simply an exercise - for example, for a course on SQL - then either design might be fine, so long as you clarify the limitations of each (eg. only one property per address).

Here is a tutorial that introduces you to relational database normalisation - which is basically the process you need to go through in order to make your decision.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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