I'm being a bit confused about a database design I'm working on. It's a real estate database.

I'm trying to store the transaction mode of a property (rent or sale). I made a lookup table called transactions so I can use Foreign keys. However each mode has additional information. Sales have the price, but rentals have the rate(ie month or semester), the price, down payment and deposit. I couldn't put them in the lookup table because there will be to much null values. So I thought of making a sales table and a rentals table both with the property id. It's a one to one relationship (one property has one transaction mode only). However I wonder if I should have the rentals id and sales id in the property table, considering the fact that one these two field might be null like this:

https://drive.google.com/file/d/127NhhhofVMzc3oyPBzHbS-5mPY2QUhEI/view?usp=sharing

or should I not put the ids in the property tab since the rentals and sales tables already have the properties id

https://drive.google.com/file/d/13CDVpQXrleNnmEUJniC8Q9ygEi-vvjvS/view?usp=sharing

Thanks for your attention

CodePudding user response：

Put the foreign key in the sales/rental tables. After all, in theory, you might be renting a property that you also want to sell. So: Rates.property and Sale.property both point to the Property table. You can then bring in the details with Property left join Rates on Property.id = Rates.property where Rates.property is not null.

CodePudding user response：

Can't you rent a property more than once?

To get the proper 1:many relationship, Rentals needs a column property_id, not the other way. (That is option 2, not option 1)

I don't understand what a "transaction" is; I don't see any other columns that would give clues.