1

I'm currently collecting the data about flat prices. I have size, price, location, price per m2 and date of retrieving the data about flat(sample date). I'm plannig create MySQL database filled with this data, which lead me to questions about appropriate schema.

I have two alternatives at my mind.

  • Put everything at one huge table

| FLAT_ID | PRICE | SIZE | PRICE_M2 | LOCATION | SAMPLE_DATE |

  • Create two tables. One with size, price, location, price per m2 and second for sample date

| FLAT_ID | PRICE | SIZE | PRICE_M2 | LOCATION | SAMPLE_ID |

| SAMPLE_ID | SAMPLE_DATE |

I'm not sure if dividing the table at such manner may bring some advantage using query. Only clear pros of two tables I saw at assumption that date consumes more memory than sample id, so less consuming of sources. On the other had I don't believe that I will have enough data to make hypothetical date column to impact the results. So not sure if it is worth it?

I want to ask advices about this situation and possible approaches.

EDIT: My goal is create the web applications with visualisations of collecting data. Like change the average price through time, average prices at observed locations. Distributions of prices and sizes and visualize the changing of this characteristic through time.

5
  • 1
    The design you're after depends entirely on your needs, what exactly do you expect from the data. It's impossible to answer it without you knowing the goal - which once you learn will likely propose the answer for the design itself.
    – Andy
    Aug 12 '20 at 13:19
  • @Andy Thank you for response. As I add at edit, I want to visualize the aggregates from data and their changes through time.
    – viceriel
    Aug 12 '20 at 13:27
  • Will there ever be more than one sample per flat? Is it OK to keep only the last sample for every flat? Aug 12 '20 at 20:39
  • Is it OK to keep only the last sample for every flat? Don't samples have any other attribute about themselves worth keeping besides the date? Aug 12 '20 at 21:05
  • @ Tulains Córdova thank you for response. To your first question probably yes. For now samples only obtain date when happened. About keeping only the last sample of same flat, i think that is benefitial record all samples of same flat, because price of flat at given point of time hopefully will lead to better aggregates.
    – viceriel
    Aug 13 '20 at 6:53
1

Since you'll have multiple samples per flat, a flat flat table (pun intended) would lead to you having duplicate data in several rows and having to somehow guarantee those columns to be in sync. You could end up with the same flat having different sizes in different rows. That's what happens when you violate 1st normal form (1NF). Also you would have to have a composite PK with ID and SAMPLE_DATE, when we know that the unicity of the flat is given by the ID. So to avoid that I propose this design:

enter image description here

You can create a view joining the two tables so you query that view to create reports and you don't have to write the join everytime.

create or replace view v_flats as
select
    f.flat_id,
    f.address,
    f.size,
    s.sample_date,
    s.price
from 
    flat f 
    left join sample s 
    on (f.flat_id = s.flat_id);
2
  • This seems to be the exactly same answer as Erik Eidt provided.
    – Andy
    Aug 14 '20 at 8:02
  • I accepted this one becaues of composite PK of sample table, which is detail not mentioned Erik Eidt great answer.
    – viceriel
    Aug 14 '20 at 11:38
3

With either of those schemes, if you want different samples of the same flat, you'll have multiple rows with the same FLAT_ID.  Essentially your primary key will be (FLAT_ID, SAMPLE_DATE).

Regarding the second, I'm not sure how SAMPLE_ID furthers the cause, unless you expect multiple rows of the first table to share a common SAMPLE with some reason/meaning of that.

You could apply normalization, and separate concerns:

Table 1, flats:

  • FLAT_ID
  • ADDRESS
  • SIZE

These seem constant — stuff you don't want to repeat over and over.

Table 2, samples:

  • SAMPLE_DATE
  • FLAT_ID
  • PRICE

PRICE/M2 is computable, so no need to store it.

3
  • Thank you for your response. I agree with your statement about primary key being(FLAT_ID, SAMPLE_DATE). But now I see that I wasn't completely clear about my situation. For now I plan sample the flats one per month, in current design, yes is possible to happened the same flat appear at july and august, but ID is not obtained during sampling process, so my current thinking is the possible same flat may have different id(i don't want to distinguish between flat manually and equality at location, price, size not gurantee it is same flat).
    – viceriel
    Aug 13 '20 at 6:45
  • Sample date will be one day per month(day when sampling happened). So my thinking was i gave this day id and this id will be part of first big table where many flats will share they day of sampling.
    – viceriel
    Aug 13 '20 at 6:47
  • But I get your point, only one thing is unclear for me, table two doesn't have primary key, or primary key will be (SAMPLE_DATE, FLAT_ID) yes? So maybe add unique SAMPLE_ID?
    – viceriel
    Aug 13 '20 at 6:57

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.