I'm focusing on a credit card applicatoin that enables customers to handle their hosting accounts. Right now, the database is simply a customers table along with a hostingaccounts table. However, I don't know what's the ultimate way to link those to tables together.
If the customers table possess a column named "possessed_accounts" in which the information is placed like "5,18,28" after which split it within the application? What suggestions have you got?
if perhaps one user could possibly be the who owns a website hosting service, make use of a foreign key on hostingaccounts like ownerid.
in case your hosting account could be possessed by multiple customers, add an additional table like hostingaccountsownerships with ownershipid, userid, hostingaccountid and fetch after that.
Produce a third table which has 2 posts: user_id, account_id. This way one user could be attached to several accounts and the other way around. That method for you to easily join these three tables to discover which accounts are for sale to the consumer etc.
if your user might have several hostingaccount you'll need a connecting table UserId+HostingAccountID as key.
Your solution works fine when the user to hostingaccounts record would be a 1-to-1 relationship, but when there's an opportunity a person might have a lot more than 1 hostingaccount then your column "user_id" ought to be within the hosting account table.
If you feel you will need a many to a lot of relationship, then you may produce a third table that holds user_id and hosting_account_id.
The very best answer really is dependent around the goals and constraints of the system.
Making use of your description I begin to see the following organizations and relations:
users( user_id (PK) ) accounts( account_id (PK) ) user_accounts( user_id references users(user_id) ,account_id references accounts(account_id) )
Based on your needs you are able to define the important thing on user_accounts diversely to enforce the restrictions:
1. primary key(user_id)
A person may own one account. A free account might be possessed by multple user.
2. primary key(account_id)
A person may own multiple accounts. A free account are only able to be possessed by one user.
3. primary key(user_id, account_id)
A person may own multiple accounts, as well as an account could be possessed by multiple customers.
If no above match your needs, you need to give more information.