Database table relationships with 3 entities

Question

I have three entities in our environment, Account, User and Company. Right now I have tables for account, account_user, account_company, company and user. It seemed fine initially but now that I think about it, it seems like it could be simplified with the joining tables.

Every Account (portfolio) has 1 or more Companies and 1 or more User managing it
User Joe Doe logs in to view Account Joe's Accounts with company(s) Joe's Company &&/|| Joe's Wife's Company

How would it be in Logical Design (designing database tables and relationships)?

Hope I described this clearly Thank you

If you have many to many relationship between account and company & between account and user, then your schema makes sense — Aman, Dec 05 '17 at 16:13

score 0 · Answer 1 · answered Dec 04 '17 at 15:41

0

This kind of schema might be of help to you.

Table Company :

Company ID NAME DESCRIPTION ETC
Table Account:

Account ID NAME DESCRIPTION ETC, Associated Company ID(foreign key)
Table User:

User Details
Table User_Permissions:

Users allowed on accounts

If you need one account to be part of multiple companies(which should not be the case) then you can make a bridge table for the account and companies too like for user permissions.

answered Dec 04 '17 at 15:41

Taha Tariq

218
1
11

This would end up with duplicate entries in the accounts table though – jono99 Dec 05 '17 at 07:00
No Duplicate entries. If you make a bridge table for accounts and companies. – Taha Tariq Dec 05 '17 at 08:02

Database table relationships with 3 entities

1 Answers1