Helpdesk Database design question

[MrRyszard]

Hello everyone,

I'm just getting re-introduced to MS access and I'm a bit rusty on my DB design.

I'm not quite sure what would be the best way to go about what I'd like to do, but I'll explain the best I can and see what we can come up with.

I'm designing an account membership database for a helpdesk. So far I've got one table "Employees" and have already populated the data

Employees
-ID
-FirstName
-LastName


-MiddleInit
-MonthBirth
-DayBirth
-Last4Soc

Ideally, I would like to use the Employees table to add in new employees, and as they get access to specific accounts, add them to the subsequent tables.

In the Account tables I would like to have
ACCOUNT1
-ID
-DateAccessRequested
-DateAccessProvided
-DateAccessTerm
-AppLogin1
-AppLogin2
-AppLogin3

Where AppLogin will contain the username for that employee, for that application as a member of that account (account1)

I have an autonum Primary Key setup for Employees, and am intending to use that ID within each ACCOUNT1, 2, 3, etc table to track logins, access modification dates, etc.

I've run into the problem where when I created a relationship between the Primary Key of ACCOUNT1 and EMPLOYEES, it generated a different primary key and created a new empty row in EMPLOYEES.


Again, please be gentile with me =P. I hate to Join and Post asking a basic question, but I'm in a pinch and would appreciate any help/guidance possible. I may be going about this the totally wrong way, and if that's the case please let me know so I can make this easier to develop in the future also as we decide to add to it.

Thanks,


Rick

[LillMcGill]

I adamantly disagree with the concept of making multiple tables: Account1, Account2, Account3.

Make a table of applications. This is a list of apps and the only fields that can go in this table are facts about apps that no app would ever have more than one of.
The relation of Employees to Apps is many-to-many.... so make an EmployeeApps table, perhaps with these fields, for example:
-EmployeeAppID
-EmployeeID
-AppID
-Login???
-Other fields that are facts about the combination of the Employee and the Apps to which they are assigned. BUT only fields where no Emp/App combo could ever have more than one of.
This means you might need more tables, but I am not clear enough on the meaning of Login1, Login2, Login3 to be certain what's going on with some of your proposed fields.

[orange]

Why not just BirthDate as a date/Year data type for Employee? You can get the month by Month(BirthDate) if/when needed.

Birth Date isn't going to change so no need to break it into parts.

Just saw the other post. Totally agree , do not use separate tables for account1, account2 , account3.

Research Normalization if you need more info.

[amrut]

From above, it is not clear how are employees and their accounts related ? How many types of accounts are there and will more be added in future ? Why there are different tables for different account types ?Further, can one employee have more than one account or more than one account types?
Post these details and someone will help you.

[MrRyszard]

Hopefully this will help.. I was WAY off and was making too much work for myself it seems.

Multiple users are on the helpdesk
Users have multiple CLIENTS
Clients have Multiple accounts within each client.


(still working on it as I type)

[rzw0wr]

Just my opinion, I think is easier when starting a new database to figure out what you want out of it before you start working on what you want to put in it.

With that in mind, could you tell us exactly what you want to get from your database?

Dale

[orange]

I agree with Dale -- always start with what you expect/need as output. That will help you focus/deduce what must go in.

Here is a tutorial that will help you -- it starts with a description of "the business" your intended database is supposed to support.
http://www.rogersaccesslibrary.com/T...lationship.zip

[MrRyszard]

Thank all you so much for the help.
I've since totally re-arranged my database and believe everything is set up properly. Once I get some downtime I'll update what I've got and see if anyone has any suggestions.
Thanks again.