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?
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.
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.
if a user can have more than one hostingaccount you need a linking table UserId+HostingAccountID as key.
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.
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.
