Appropriate database schema

Question

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.

Answer 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:

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);

Answer 2

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.