/    Sign up×
Community /Pin to ProfileBookmark

How to make a database design decision?

Hello,

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

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). 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 and just fetch data in a joint query later on:

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

Or should I design it another way. Thanks for your attention

to post a comment

13 Comments(s)

Copy linkTweet thisAlerts:
@sibertOct 06.2021 — > @Salvignol#1637905 Or should I design it another way. Thanks for your attention

From top of my head, I should consider only three tables:

  • 1. Propreties

  • 2. Transactions ( former rental + sales)

  • 3. Type of transaction (rental or sale type connected to the transaction - not the property)


  • You either sell or rent (type of transaction) a property. Or both

    I can also think of a deposit when you sell. So that column can be used by both rental and sales.

    @NogDog may have a better suggestion :-)
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 06.2021 — @sibert#1637907 Did you mean something like this?:

    https://drive.google.com/file/d/18KblZzpjLyfrC6j5p-RCE7m3C_folBw5/view?usp=sharing
    Copy linkTweet thisAlerts:
    @sibertOct 06.2021 — > @Salvignol#1637910 Did you mean something like this?:

    Yes, do you think this will work for you? But you do not need a transaction_id in the property table. As I see it, there is **one property-many transactions** relation. You only need the property_id in the transaction table. And you may need both **category** and **users** table as well.
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 06.2021 — @sibert#1637911 I like your design better I won't have to create a third table for both rent & Sales. However, I was worried about NULL fields mostly, I heard NULL aren't really a good thing. Like for example if a user only wanted to sell a house, which, lets say, only needs price and deposit, the rate and down_payment fields will be NULL. Isn't it a problem??
    Copy linkTweet thisAlerts:
    @sibertOct 06.2021 — > @Salvignol#1637913 Isn't it a problem??

    When you create the table, you can steer up what should be saved or not. This constraints and defaults syntax may differ from database to database. You have to consult your database manual for this. Just pseudo code something like this:

    ``<i>
    </i>CREATE TABLE table(
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    first_fk INT NOT NULL default(0)
    second_fk INT NOT NULL references author(id)
    trans_date NOT NULL default now()
    );<i>
    </i>
    ``
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 06.2021 — @sibert#1637916 ok so it basically mean I should use 0 for that down__payment field, if it's not specified?
    Copy linkTweet thisAlerts:
    @sibertOct 06.2021 — > @Salvignol#1637917 so it basically mean I should use 0

    Yes, but a stored NULL value does no harm until you use it :-)
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 06.2021 — @sibert#1637918 Ok thanks a lot. And is the a difference between None and NULL?
    Copy linkTweet thisAlerts:
    @sibertOct 06.2021 — > @Salvignol#1637919 And is the a difference between None and NULL?

    A column can only have a value OR no value (NULL). Never heard of "none value" in a SQL table :-)
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 06.2021 — @sibert#1637921 Ok. I just needed clarifications thanks again
    Copy linkTweet thisAlerts:
    @NogDogOct 07.2021 — > @sibert#1637907 [@NogDog](https://www.webdeveloper.com/u/NogDog) may have a better suggestion :-)

    I did start looking at this, but then got distracted by my job. Without digging into all the discussion (which looks fruitful?), I'd probably need some clarification of the actual meaning of some of these things, some of the "business rules", etc. to be sure. For instance, can a single property have both a "Rentals" thing and a "Sales" thing associated with it, or is it always just one or the other? Are you actually tracking "transactions", i.e., something that _happens_ related to the property, presumably with a date or datetime associated with it? (Names are important. ;) ) In other words, could there be multiple sales and/or rental "transactions" associated with a property, or are you really just storing the current "asking price" to buy or rent that property?

    Ultimately, you want to nail down which things have 1-to-1 relationships, which have many-to-1 relationships, and which have many-to-many relationships; which then drives the DB design.

    On a side note, at least if using MySQL, you could use an [ENUM type](https://dev.mysql.com/doc/refman/8.0/en/enum.html) for the transaction_type (or whatever you call it if it's not really a transaction ;) ), rather than creating a separate table to essentially specify your types.
    Copy linkTweet thisAlerts:
    @sibertOct 07.2021 — > @NogDog#1637932 you could use an [ENUM type](https://dev.mysql.com/doc/refman/8.0/en/enum.html) for the transaction_type

    Or, you can just use 0 and 1 to represent the sales / rent type. :-). Like a boolean sort of.

    The benefit of a separate type lookup table is that the types theoretically can grow. Different types of rental. Different types of sales etc. With a separate lookup table, you can add types on-the-fly. But this is only if it ever is needed.
    Copy linkTweet thisAlerts:
    @SalvignolauthorOct 23.2021 — @NogDog#1637932 Sorry for the long delay but since webdeveloper.com couldn't notify me, I didn't see this reply until now that I visit the site in a long time. I would have answered a lot sooner. The project have been changing since the last post so I have more questions, but first I'm going to answer these:

    Question1: "For instance, can a single property have both a "Rentals" thing and a "Sales" thing associated with it, or is it always just one or the other?".

    I realized down the line that a single property is either for rent or for sale, never both it is always just one or the other (Imagine somebody buying the apartment while somebody else is already renting it 😅, what happens then). However once a property has been sold, it can be later uploaded as a property to rent buy the buyer.

    Question2: Are you actually tracking "transactions", i.e., something that happens related to the property, presumably with a date or datetime associated with it? (Names are important. 😉)

    Indeed names are important here. If you could illustrate more what you meant by transaction that'll be helpful 😅.

    Question3: "could there be multiple sales and/or rental "transactions" associated with a property or are you really just storing the current "asking price" to buy or rent that property?"

    I think I am just storing the current "asking price" to buy or rent that property. However this question also makes me curious: "could there be multiple sales and/or rental "transactions" associated with a property". This question actually makes me wonder whether real estate platforms keep tracks of the properties sold or rented.

    thanks for your attention and Sorry for the long post 🙏.
    ×

    Success!

    Help @Salvignol spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 BETA 3.29,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,

    tipper: Anonymous,
    tipped: article
    amount: 10 SATS,
    )...