CODEWITHSUNDEEP
phpmysqldatabasedatabase-designweb-applications
Daniel Sorichetti
Daniel Sorichetti

Reputation: 1951

Database design: User with access to specific pages

I am working on an application that allows users to manage their hosting accounts. At the moment, the database is just a users table and a hostingaccounts table. However, I do not know what is the best approach to link these to tables together.

Should the users table have a column named "owned_accounts" where the data is inserted like "5,18,28" and then split it in the application? What suggestions do you have?

Upvotes: 1

Views: 234

Answers (5)

Ronnis
Ronnis

Reputation: 12833

Using your description I see the following entities and relations:

users(
   user_id (PK)
)

accounts(
   account_id (PK)
)

user_accounts(
   user_id    references users(user_id)
  ,account_id references accounts(account_id)
)

Depending on your requirements you can define the key on user_accounts in different ways to enforce the constraints:

1. primary key(user_id)

A user may only own one account. An account may be owned by multple user.

2. primary key(account_id)

A user may own multiple accounts. An account can only be owned by one user.

3. primary key(user_id, account_id)

A user may own multiple accounts, and an account can be owned by multiple users.

If none of the above fit your requirements, you have to give additional information.

Upvotes: 0

jamesmortensen
jamesmortensen

Reputation: 34038

Your solution would work fine if the user to hostingaccounts record was a 1 to 1 relationship, but if there is a chance a user could have more than 1 hostingaccount then the column "user_id" should be in the hosting account table.

If you think you'll need a many to many relationship, then you could create a third table that holds user_id and hosting_account_id.

The best answer really depends on the goals and constraints of your system.

Upvotes: 0

Dani
Dani

Reputation: 15069

if a user can have more than one hostingaccount you need a linking table UserId+HostingAccountID as key.

Upvotes: 0

Samuel Herzog
Samuel Herzog

Reputation: 3611

if only one user can be the owner of a hosting account, use a foreign key on hostingaccounts like ownerid.

if your hosting account can be owned by multiple users, add an extra table like hostingaccountsownerships with ownershipid, userid, hostingaccountid and fetch from there.

Upvotes: 2

pablochan
pablochan

Reputation: 5715

Create a third table that has 2 columns: user_id, account_id. That way one user can be connected to several accounts and vice versa. That way you can easily join these three tables to find out which accounts are available to the user etc.

Upvotes: 0

Related Questions