Table Design (1 Viewer)

[iain1978]

Hi,

Bit of a long one but I require some advise if people can supply it.

I've started creating a DB that is now getting out of hand due to me being inexperienced. The things I want to do now are getting way to complicated. From reading various posts I know that this is all being caused by my database design and while the DB is still young I'm go back to square one. I want your advice on this if you can to ensure 3 weeks from know I'm again not chasing up weird bits of VB code to make it all work.

I basically have a lot of information to hold that interconnects with each other. I'm making up a role profile DB (so we can assign define access rights to a computer system) I have lots of info to collate but the basic stuff is.

Staff Records (Name, business area & ID)
Applications (What computer applications do staff require)
Shared Drives (What access to shared drives and folders do they need)
Shared Mailboxes (what access to shared mailboxes)
Roles (users will be assigned roles to suit their requirements)

Basically what I have at the moment is below.

Roles
Role ID
Role Name
and about 15 other fields that dont really matter for this

Applications
Application ID
Application

Shared Drives
Shared Drive ID
Shared Drive

Shared Mailbox
Mailbox ID
Mailbox

Each role will only have a select number of applications, mailboxes and drives from the overall list. so role 1 may give access to 2 of the 10 applications, 1 out of 5 shared drives and 3 out of 4 mailboxes. Role 2 on the other hand may need 9 out of the 10 applications, 1 out of the 5 shared drives and 1 out of 4 of the shared mailboxes.

I just don't understand how I can create a form from these tables that would display all the different roles with their respective apps, mailboxes and drives they would need.

any advice you can give to help whould be appreciated.

Again sorry for the long post.

 

[The_Doc_Man]

I just don't understand how I can create a form from these tables that would display all the different roles with their respective apps, mailboxes and drives they would need.

What I don't see is what ties all of that together. I would expect to see something like a junction table between each item and the ROLE table. An example would be

tblRoleApps
RoleID
AppID

Where an entry here means that persons in the indicated role can access the indicated APP. (If you have a particular "flavor" of access, like ReadOnly or ReadAppend or FullAccess, etc. etc, a third parameter in this table would identify the flavor.)

But if you are looking at this right, you don't need a separate table for Apps and another for Drives and another for Mailboxes.

You need ONE table with an ID for the record and a type-code for the thing it describes - App, Drive, Mailbox - and a field for the name of the object (whatever it is).

THEN you can create a query (or at most, a two-layer query) that links role to security object THROUGH this linking or junction table.

Now if you do that, you can build a report on the final query with breaks in any order you want - by user first, by object-class first, by object-name first, by whatever you want.