Hi,

I'm struggling here in designing my tables. I'll explain what I'm trying to achieve as it will probably be easier.

I have 2 design questions:

1) You can be a private user, and therefore are not part of a company or you can be a company.

If you register as a private user, you will just need to register various details such as username, loginid, e-mail, etc...

If you register as a company, you will need to register company name, address, etc...

So far, so good everything is fairly straight forward but when you register as a company, you will also register a single user first that will act as an administrator and again, this part is ok.

This is the part where I'm struggling:

I want to allow the company to define one or more groups and assign various access rights, but I'm not sure where to go next.

I started defining a table with the usual GroupID, GroupName, etc... and I want to ensure that the GroupName is unique, so that if you create an Administrator group, you won't be allowed to create another one and I was going to just have another table called GroupAcces where you would define a relationship with a company i.e. CompanyID & GroupID, but then I realised that going that way was not a solution as I would not be allowed to create another Administrator group for another company. Am I making sense so far?

So instead of having a Groups table defined as explained I'd thought I'd remove the GroupAccess table and modify the Groups table and add the field CompanyID to it, so you end up with CompanyID, GroupID, GroupName, etc... but then what??

Do I set the CompanyID & GroupID as a primary key and create an index and set the CompanyID & GroupName as unique key so that you cannot have two group name with the same name for a given company but it would allow me to define a groupname i.e. administrator for example, for more than one company.

Is this an appropriate design? Something doesn't feel right but may be it is?

Can someone clarify this for me or provide me with a better design for this?

2. Also, I want to assign access rights to my groups when you register as a company, but I want to offer the same access rights to a private user so these would not be applied at the group level. How would I go defining this.

Any help would be greatly appreciated!

Thanks.

T.