Hey database experts. MySql question:
I have a table with houses, there is the location, color, etc. Shall I make a column for the owner or make a separate table HouseToOwner - where I connect the id's? I have no idea why is it good practice but saw it in few cases. I have the same with the things inside like I would just make a boolean column for a TV-like haveTV - true/false. Tell me why to not fill everything in one table. thx

If the owner can have multiple houses, then yes move the owner to another table "home owners" and carry the id though as a foreign key.

SELECT
h.Id,
h.address,
o.name
FROM houses h
JOIN home_owners o ON (h.owner = o.id)
WHERE h.zipcode = 5555;

@C0D4 but every house have already a row, and a house can have only one owner so it's not less hustle to simply put the same owner for many of them?

@blindXfish except if you need to know the home owners details.

Like you want to know there phone number to call them about 3 properties they own.

Storing that data seperate reduces redundant data where every home would need to store it, and be maintained with any changes. Having a single source of truth for relational data is the point of a relational database.

A single house (1) can have a single owner (1) that's a 1:1 relationship.

But... can a single owner (1) only have a single house (1) or multiple (n)?
If it's (n) split the data.

As for the furniture side of this, you could have a "furniture fittings" table and be a table between the houses (id) and furniture type (Id) - another table again and hold a quantity field.

Or if it's expected to be text fields (someone enters "TV") then you could just leave it at that.

@C0D4 I get your point, but is it beneficial? I mean I will have one table less without doing it? I do not really know what will MySQL prefer. To make clear, houses are predefined, there won't be more or less in the future, so all of them shall have a row anyways. Is there a golden rule for this?

@C0D4 I see. So Because I have multiple users, but predefined houses, for finding the houses of the users it's better to have a separate table where I connect the house id with the owner id - but then I get an exact same table like the houses just with fewer rows. Is it still okay?

@blindXfish a relational database (like MySQL) is built for this type of data structure.

Is it beneficial? Yes
You can query your data with a lot of flexibility if done right.

You could see how many tv's 1 owner has across all their homes. - for example with only a single query and no extra processing on server side to manipulate the data to come to the same results.

@blindXfish this is my day job with e-commerce 😏

Don't think of the data as how you will insert it, think of the data as how you will maintain it when something changes.

Like an owners phone number needs to change, are you going to update 30 home records, or a single owner record?

@C0D4 @C0D4 Nah, of course, the users/owners have their own table. :) But I see what are you talking about, so it doesn't really matter that I have to make a new row for every house owned by the same user? It's not slower?
-----------+-----------+-------------+
id | UID | HouseID
-----------+-----------+-------------+
0 1 15
0 1 16
0 1 68
Simply because to check who is the owner I have to join 3 tables, the house where userToHouse.HouseID == houses.id and also where user.UID == userToHouse.id , otherwise I could just read it from the house. I don't want to make you angry, just want to understand it fully.

@blindXfish having all the data on a single record will get slower over time with 1000's or 100,000's of records. for small numbers, it would work fine, just be tedious maintaining duplicate data when you can simply branch of to another table that contains that data.

i like to think of tables as entities theres always many of them. but each unique.

heres a mockup schema and sample queries you could use to see what im talking about.

https://gist.github.com/C0D4-101/...

@C0D4 Thanks. This is exactly how I wanted to make it. :D What I saw before is that someone is using a third table to join the owners with the houses, but it's not the case here. Anyways I will continue this way. Thank you!

@blindXfish ah, no dont do that. its not needed and generally slows you down if done wrong adding mapping or joining tables when its not required.

look into normalization, generally speaking if you need to go further then 3, sometimes 4nf your doing something wrong and should rethink the data structure, thats not to say joining 20 tables together is bad, it can be the right solution at times, but at that level your data needs to be still usable.

https://guru99.com/database-normali...

Just a warning....

Most RDBMs can only utilize one index *per query* per table

Yes there are optimization techniques which try to bypass this, but they most often have a ridiculous amount of conditions.

Hence, when you store all referential columns in one table, the chance to have a good index for filtering is near zero.

That's the primary reasons why avoiding joins and / or associative tables is a long term mistake.

Not saying that this is relevant in this case, but just a reminder as this is a common pitfall.

Hence if you have 3 reference columns in one table, like
ID_Alpha
ID_Beta
ID_Gamma
and each of them has an index...

And an query uses all 3 columns - only one of the three columns can be used as an index.

Compound indexes (index consisting of multiple columns) are a workaround that has it's own shortcomings.

If the table is fairly small and fits into RAM it's never a problem.

As soon as it hits thousands of rows, it would be ... Suboptimal :)

@C0D4 One more question, it's a bit off topic but ok. I have plots which have some resources like 0.6 coal. If the owner builds a mine, depending on the lvl of the mine he will get 1*0.6 oil/day. So user will have amounts of those resources stored in his own row. The question is where shall I increment this data?

I see there are multiple options, like cron, mysql event schedule, I never did something like this

@blindXfish I mean you could use a schedule event, I've always ran cron jobs with explicit tasks, but there's usually some data processing required or events to trigger.

For you're example though updating a value based on a stored multiplier you could get away with using the scheduler if all the data is readily available.

Does each level of the mine increase the multiplier by 1 or 0.5 or something different per level, and is this multiplier stored somewhere in the DB for reference or in the code base?

Which ever you choose to use (cron or scheduler) be consistent, so that if you ever get to a day of "wtf is updating this record every 23 seconds" you'll have a good idea where to find it.

@IntrusionCM there's nothing like seeing several left joins taking forever to run when they are all of the same table. 😢

Why must people make horrible decisions when it comes to data.

@C0D4 "it's the DBMS XY's fault. When we switch to DBMS Z, everything will work much better. Shouldn't be that hard, it's just SQL, right?"

*sigh* sadly thats whats happening most of the time instead of fixing the bukkake they call "data(-base) design*