Database design: embedding account attribute in every table or using joining table
In my database, the account table maintains all accounts registered to my site. there are also a set of resources which are maintained in resource table called Resource1, Resource2 ... ResourceN, all resources are belonged to some account.
now the problem is should I embed id of account as a column in each table? or using a joining table for each resource? I personally like joining table but not sure which is a better design for this kind of problem.
Using a "joining table" would be more flexible in terms of relationships, e.g. you can have a 1-to-1, 1-to-Many or Many-to-Many relationships between your resource and account records, although enforcing these relationships can become complicated. If you are not sure what kind of constraints these relations have or will have, I would go with the "joining table" idea.
How about this design from what I understand from your question.
We create three tables, Accounts, AccountResources, Resources. Using an Association Table would simplify the queries as well.
Table: Accounts Columns : AccountID
Table: AccountResources Columns : AccountID ResourceID
here AccountID and ResourceID form the composite key.
Table : Resources Columns : ResourceID