Design question

It must be done like this because each site may not always be on the network, but the data must always be available from the central server.

To enable the master database to contain all the site database information, I use a composite key, with the first part being the site identification, and the second part being the row identification. This allows user with row id 5 for instance to exist more than once because paired with the site id it is still unique.

On top of this, there is another layer. Each site must be contained within an "Enterprise", which is just another identifier. So, I have a table called enterprise which contains the list of site ids.

So far then, I have this (obviously cut down to only show the relevant columns) : -

table: enterprise
column: id

table: site
column: id

table: user

column: site_id
column: id
column: label

With the site_id and id making the composite key.

Now, I've just had to add the concept of "enterprise" level users. Initially the only clean way I could think of doing this is having two separate user tables; enterprise_user and site_user : -

table: enterprise_user
column[key]: enterprise_id
column[key]: id
column: label
etc..

table: site_user
column[key]: site_id
column[key]: id
column: label
etc..

However this does not seem very "normalized"..

Other ways I considered are : -

table: user
column[key]: id
column[key]: enterprise_id
column: site_id
column: label
etc..

But this does not make sense as an enterprise user is not associated with any site. I could have a dummy site, but I really do not like that. I could just set site_id to NULL, but I'm not a big fan of NULL in database and it just seems a bit hacky.

So then I thought of adding a link table:

table: user
column[key]: id
column[key]: enterprise_id
etc..

table: user_site <== link table

column: enterprise_id
column: user_id
column: site_id

with a unique constraint on enterprise_id, user_id (the composite key that identifies a user in an enterprise).