What is the optimal DB design for this type of data

Question

I have a table of Users,

Each USR_ID can have multiple Roles,Views,Brands and Units.

So the optimal design I thought of is this:

Users
USR_ID    USR_Username    USR_Password

Matrix
M_ID    M_USR_ID    M_ROLE_ID    M_VIEW_ID    M_BRAND_ID    M_UNIT_ID

_ROLES
ROLE_ID    ROLE_Name    ROLE_Active

_VIEWS
VIEW_ID    VIEW_Name    VIEW_Active

_BRANDS
BRAND_ID    BRAND_Name    BRAND_Active

_UNITS
UNIT_ID    UNIT_Name    UNIT_Active

So basically in the Matrix table I keep all data regarding which user does what.

My question is, is that the optimal structure and design? further to that, how do the rows in the Matrix table will look like? This is an example of someone that is has ROLE_ID = 1

Matrix
1    30    1    4    7    2    4

Now that I want to add another ROLE to that person, what will the next row look like? Do I keep the values of all other definitions (brand,unit,view...)? Or do I just insert everything with NULL except the ROLE_ID?

Your assistance is much appreciated,

Thanks,

Answer 1

I would not use your matrix table. Instead, use cross reference tables.

To link Users and Roles:

User_Roles
USR_ID    ROLE_ID

Create across reference table for each table: Roles, Views, Brands, Units. This way you can have multiple units, for example, for a user without tying each to a particular role. And you can have three units and three roles and three brands without creating 27 matrix records.

Answer 2

(start of reply deleted because Scott said it a minute faster and more clearly than I did.)

Sounds like the matrix design is too flat for the data you have; you should definitely consider using a more normalised design.

What's not clear from your question is whether there's any dependency between Roles / Views / Brands and Units. It's worth watching for this as you design the table - for a silly example with your data: if a user with a given Brand always needs the same Units as well, and there's no other reason why users have Units, then you don't need to connect the Units to Users at all, just to Brands. But that's a refinement.

Your starting point should definitely be the design Scott suggested: One table each for Users, Roles, Views, Brands, Units, and four cross-reference tables connecting Users to each of the other four.