-2

My application has 2 types of users, so I'm employing one of the standard approaches to design the schema:

Table users (holding fields shared between both user types):

+----+----------------+---------+
| id |     email      | type_id |
+----+----------------+---------+
|  1 | foo@bla.com    |       1 |
|  2 | bar@bla.com    |       2 |
|  3 | bla@foobar.com |       1 |
+----+----------------+---------+

Table user_types:

+----+----------+
| id |   name   |
+----+----------+
|  1 | person   |
|  2 | company  |
+----+----------+

Table persons (first user type):

+----+---------+------------+-----------+
| id | user_id | first_name | last_name |
+----+---------+------------+-----------+
| 21 |       1 | John       | Smith     |
| 23 |       3 | Sohn       | Kmith     |
+----+---------+------------+-----------+

Table companies (second user type):

+----+---------+--------------+
| id | user_id | name         |
+----+---------+--------------+
| 31 |       2 | Company Inc. |
+----+---------+--------------+

The user_id field in persons and companies is a foreign key referencing users.id.

Now, at some point I need to create user profile pages, and the URLs will use the ID of the user, like this:

  • /company/[ID]-company-inc
  • /person/[ID]-john-smith

My question is:

Which ID should I use to identify the user? Which ID should be the "user ID" that will be used across the app?

Should it be the one from the users table (users.id), or should it be the one from one of the respective tables (persons.id or companies.id), as they both have their own primary keys?

My assumption is that every table should have a primary key, so I would go with the ID from any of the user-specific tables - persons or companies.

Is this approach correct?

Community
  • 1
  • 1
lesssugar
  • 15,486
  • 18
  • 65
  • 115
  • I might be misunderstanding, but it seems like you're implying that since `persons` and `companies` are the "first and second" user types, there will only be one entry *per user* in each of those tables. Is that correct? If so, then I'm not sure why those are separate tables at all. (In any event, the `users.id` is the one to use) – aaronofleonard Sep 19 '16 at 15:13
  • @Amleonard The user tables are separated mostly for performance reasons. The 2 user types are very different (the tables above are simplified), so if I wanted to keep all the user information in `users` table, the row would get very wide. Instead, I want to keep it clean, especially that there might be more user types in the future, and adding them will be easier if they are logically separated. Having a "short" `users` table increases logins speed as well. --- And about your suggestion - `user.id` you say. Can you write why? Won't the primary keys in `persons` and `companies` be useless then? – lesssugar Sep 19 '16 at 15:26
  • The primary keys may be useless, which is okay. You could even just reuse the user ID as the primary key in those tables, so you only have one ID. The benefit of user ID is that it's unique and it won't require an extra step to get to the user table. If you use person/company ID, then you have to first look up the person/company, then get the user ID (or you could use a join -- either way, you are looking at three tables, which negates the value of a small user table in the first place). – siride Sep 19 '16 at 16:01
  • I don't think it really matters which ID you use in the URLs. Either way you're going to have to join the two tables. – Barmar Sep 19 '16 at 16:06
  • @siride Makes total sense. However, this makes me think whether this separation is needed at all, as the same ID will be duplicated in 2 tables - users and companies, and users and persons. Bit of denormalization, I would say, and I have no real proof that the separated tables will better the overall queries performance. – lesssugar Sep 19 '16 at 16:10
  • @Barmar When you keep in mind that the tables would really need to be joined in 2-3 cases, this doesn't look like an overkill. But I see your point. Thanks. – lesssugar Sep 19 '16 at 16:12
  • It doesn't seem like de-normalization, because there are attributes that only make sense for one or the other type. Users have a birthday, companies have a CEO. – Barmar Sep 19 '16 at 16:27

1 Answers1

1

I think for a user you will have only one person or only one company. If this is the case I recommend to use as id in company and person table the same as from user_id

So for example the companies table will look like this:

+----+--------------+
| id | name         |
+----+--------------+
| 2  | Company Inc. |
+----+--------------+

So users.id = companies.id

And the same for person table.

And this way it's clear what id to choose because it's the same in all the tables and will not create confusion.

If you don't want to change tables like this I think you should choose the id from second level table. So for company to chose the id from that table and call it company_id (in code, not mysql) and the same for person. This way the code will be clear to what id you are referring.

Daniel Dudas
  • 2,972
  • 3
  • 27
  • 39
  • So, in your initial idea, you suggest to make the `company.id` a foreign key referencing `users.id`, or make it a hard-coded primary key? – lesssugar Sep 19 '16 at 15:41
  • I suggest to make `companies.id` both primary key and foreign key to `users.id` – Daniel Dudas Sep 19 '16 at 15:49
  • 1
    Thanks for clarifying this. Interesting, I wasn't sure whether you could actually combine a PK with FK. A good post on it: http://stackoverflow.com/questions/8949028/mysql-using-foreign-key-as-primary-key-too. Also, I need to reconsider separating the users into different tables. Not 100% sure if this is the right approach in the end. – lesssugar Sep 19 '16 at 15:58