MySQL modelling task

Question

I have next task to do in MySQL:

We have Accounts, Farms, Customers, and Users
An account belongs to a Customer
A User has access to one or more accounts
A User has access to one or more farms tied to that account
A farm is tied to one account

Here is ER Model:

Can someone take a look and check whether I made this correct?

`users` and `customers`, in-order to avoid redundancy have only a single table `users` and add a column named `role` in `users`. — Hamid Ali, Jul 04 '20 at 01:26
right! then this is the relation between `users` and `customers`. `Each customer is a user` and `a user can be a customer` — Hamid Ali, Jul 04 '20 at 01:30
yep, based on the information above I've given you suggestions. — Hamid Ali, Jul 04 '20 at 01:35

score 0 · Answer 1 · answered Jul 04 '20 at 01:45

0

For this SQL modeling task, you have three types of entity relationships: one-to-one, one-to-many and many-to-many (How to implement one-to-one, one-to-many and many-to-many relationships while designing tables? for more detail on their implementation).

In your toy model, if "a User has access to one or more accounts," this is modeled as a one-to-many relationship between the user and the accounts, which requires a foreign id on the many side (i.e. every account has a user_id) but user does not have an account_id.

The account-customer relationship appears to be well-modeled, because it is one-to-one (so either table can hold the foreign key), but your one-to-many relations appear to have some errors.

answered Jul 04 '20 at 01:45

Holden Rohrer

586
5
16

Where do you think I should use many to many? Can you say what errors in one-to-many? – nenad Jul 04 '20 at 01:47
But users can access many accounts. Why should user have an account_id? – nenad Jul 04 '20 at 01:52
@nenad 1) you should model the relationships that you need to. You don't necessarily need to model all kinds of relationships, but I mention all of them for completeness. 2) Please check all your relationships through these lenses (some may be correct but not all); I don't want to give you an easy answer because this looks like a homework question to me – Holden Rohrer Jul 04 '20 at 01:56
Can you please review this part of your answer: "In your toy model, if "a User has access to one or more accounts," this is modeled as a one-to-many relationship between the user and the accounts, which requires a foreign id on the many side (i.e. every account has a user_id) but user does not have an account_id." – nenad Jul 04 '20 at 02:05
I put foreign id on the many side and user does not have an account_id. I've alredy did it like you sad. – nenad Jul 04 '20 at 02:07
@nenad This means one user -> many accounts (or, equivalently, each account is related to exactly one user but not the other way around). You may have done this piece correctly, but I urge you to check *all* of your relations. – Holden Rohrer Jul 04 '20 at 02:07
Yes, I will. You should have written something useful but thank you in any case. – nenad Jul 04 '20 at 02:10
@nenad Look at your ***other relations*** – Holden Rohrer Jul 04 '20 at 02:14
I will. Thank you. – nenad Jul 04 '20 at 02:15

MySQL modelling task

1 Answers1