1

Let's say I have a table called users that contains basic user information like email and password and I also have a table named clients.

A client is a type of user, so that it will have a field that will hold the id of the corresponding user from table users, a field like user_id.

Now le'ts say there are tables that are specific to the client and then there will be the need to store the id of the client, like so:

Users table:

Clients table:

id Name user_id
1 User 1 1
2 User 2 2

Courses table:

id Name client_id
1 Course 1 1
2 Course 2 2

Now what I really don't know is that what id should I save in Courses table.

Should I save the client_id from clients table or should I save the user id from users table as client id.

I really dont know because a client is a type of user and only a client can buy a course, not all types of users, on the other hand, a client doesn't exist without a user what's really confusing is that if I use the user id instead of the client id then the field should be called "user_id"? But then it would be generic as only clients can have a course.

So what's the best approach:

1 - Name the field client_id and add the id of the client from clients table and get the user related to that client afterwards (I'm inclined to this one).

2 - Name the field client_id and store the id of the user from users table and get the associated client (This is a bit more confusing but could make sense hierarchically speaking since there's no client without a user).

3 - Name the field user_id and store the id of the user from users table (this approach really breaks the semantic in my opnion since user_id could be any user but only user of type client could have a course).

-----------------------------UPDATE-------------------------

So I think my question wasn't clear so I will clarify.

What I want to know is that if there's any best practice on this topic because the way I store and retrieve data would impact directly the way I retrieve data in the app due to the relationships.

Now, In a real case scenario where I have 3 tables: table users, table clients and table courses.

The table clients only hold the fields that wouldn't fit in the users table considering that I will have more type of users that will be in other tables too, like administradors which is also a type of user.

Now, considering a client wouldn't exist wihout a user and considering only clients can make purchases to buy a course for example, which id should I store in client_id in the courses table, the id from the table clients or the id from the table users.

For example only clients can buy products but a client (clients table) belongs to a user (users table). In table, say purchased_products that contains a field named client_id, should I have the id of client from table clients or the id of the user from table users?

Both make sense to me and I would be able to retrieve the data regardless what I really want to know is what makes more sense here:

1 - If I store the id from table clients in courses.client_id, I would retrieve the user like this: $client->user which hierarchically doesn't make sense since a user comes before a client and not the other way around but at least it would be the id of the table client stored in courses.client_id.

2 - If I store the id of the table users in courses.client_id, I would retrieve data in a way that makes more sense like $user->client but I fell like the name of the field should not be courses.client_idbut rathercourses.user_id` but then it would also be bad because not all users can buy a course so might seem misleading.

In the first example $client contains all informatin related to the table clients and $client->userwould access all information related to the tableusers` for that client.

In the second example $user contains all informatin related to the table users and $user->clientwould access all information related to the tableclients` for that user.

Again it's more of a semantic question to make things more organized.

0

1 Answer 1

3

... only a client can buy a course ...

Bingo!

There you have a Business Rule that states that the relationship is between [Purchased_]Course and Client, not [Purchased_]Course and User. So you would store the Client's key in the Purchased_Course table.

And yes; I have added another table here.

Remember that a Course can exist in its own right, without anyone purchasing it, so that needs a table of its own. The relationship between the Course and the Client when they purchase it needs a third table.

select * 
from Clients ; 

+----+--------+---------+
| id | Name   | user_id |
+----+--------+---------+
| 66 | User 1 |       1 |
| 77 | User 2 |       2 |
+----+--------+---------+

select * 
from Courses ; 

+-----+----------+
| id  | Name     | 
+-----+----------+
| 888 | Course 1 |
| 999 | Course 2 |
+-----+----------+

select * 
from Purchased_Courses ; 

+-----------+-----------+
| course_id | client_id | 
+-----------+-----------+
|       888 |        66 |
|       999 |        77 |
+-----------+-----------+
2
  • Thank you for your answer. So I should store the client's id from Clients table in Purchased_Courses even if a client can't exist without a user, right? So I would retrieve the user based on the client's id like so: client->user ...yeah I get it but somehow doesn't make sense to me, hierarchically speaking, since the user comes first. Haha but anyway, thank you once again. Feb 21 at 19:15
  • 1
    Yes, User "underpins" Client and a Client cannot exist without a User. BUT you could have a User that /isn't/ a Client (and therefore, cannot purchase courses) but can do "other stuff" within the Application. Model the Business Rules as far as you can.
    – Phill W.
    Feb 22 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.