MySQL Table Setup

Question

Hey all, I am setting up a PHP web app that will make use of subdomains for accounts. I am storing subdomains in a MySQL table with the following fields:

subdomain_id | owner_id | name | date_created

owner_id maps back to user_id in the user table The user table has the following fields:

user_id | email_address | etc...

Now I am trying to figure out what is the best way to store which users have access to which subdomain. Is the best to set up another table with the following fields?

id | subdomain_id | user_id

That would contain data such as the following (showing user #6 has access to subdomains 4 & 7):

id | sudomain_id | user_id
1  | 4           | 6
2  | 4           | 23
3  | 7           | 6

Is there a more efficient way?

Dan Grossman · Accepted Answer · 2011-03-03 03:07:08Z

2

That is the correct way to model a many-to-many relationship, but the id column is entirely unnecessary. You don't need to give every table an artificial identifier. The primary key of that table is simply (subdomain_id, user_id).

answered Mar 3, 2011 at 3:07

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zoidberg Over a year ago

I agree with Dan to a certain extent. Surrogate keys are often not necessary but are not a bad practices, especially if that table's role grows to more than just a relation table in the future. Perhaps permissions will be attached to a user's access to a subdomain in the future, then this table will need to have a 1 to many relationship with a permission table. If I were you, I would leave the surrogate key in the table.

Hutch Over a year ago

@Zoidberg agreed. I intend to leave the surrogate key, but understand what Dan was getting at.

Zoidberg Over a year ago

if this answer is satisfactory, be sure to accept it as the correct answer.

Hutch Over a year ago

@Zoidberg will do, giving me a 6minute wait time to accept however.

Zoidberg Over a year ago

I did not know that, learn something new every day, good luck with your database design

Collectives™ on Stack Overflow

MySQL Table Setup

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related