CODEWITHSUNDEEP
mysqldatabase-designentity-relationship
Darmen Amanbay
Darmen Amanbay

Reputation: 4871

Database relation concept

Good morning, experts

I need your advice. Let's say we have user, profile and post entities.

Variant 1: user has many profile, profile has many post

Variant 2: user has many profile, user has many post

What's the best? By convenience, "right way", or maybe even performance?

Upvotes: 0

Views: 99

Answers (3)

iDevlop
iDevlop

Reputation: 25252

Option 2 seems much better to me.
But specifying what kind of information your store in a profile would help understanding.
I assumed that profile holds role/category information.

Upvotes: 1

Aishwar
Aishwar

Reputation: 9714

It depends...

1) on how the system works..if the posts are associated with profiles. Then you should use Variant 1. For example, the questions I post in Stackoverflow should be associated with my Stackoverflow profile. The questions I post in Superuser should be associated with that profile.

If you want to maintain this type of behaviour with Variant 2. The post would have to have a field indicating where the post belongs.

2) on how you will use the data. If you do not need the profile information when querying for posts, then may be you shouldn't associate profile and post.

3) In terms of performance, using very basic logic, joining across 3 tables to retrieve some information is likely to be worse than joining across 2 tables (of course depends on the columns in the join, index etc...but say all else being equal).

I think the best thing to do is to keep it simple. Keeping it simple, it'll be easier to figure out if something will work the way you expect it to. If it doesn't, it will be easier to figure out why and fix it. And if required, it'll be easier for you to modify it later on as well.

Upvotes: 2

hash1baby
hash1baby

Reputation: 131

Assuming you mean to ask: "In general, when designing a data model, what considerations should be valued highest - convenience, correctness, or performance?", then I would say that correctness should be a first priority. Convenience and performance will be secondary concerns.

Upvotes: 1

Related Questions