Recommended Table Construction

[milkman2500]

Hi,

I'm trying to figure out the best way to layout my tables. Each object has it's own table (i.e. "t_employees", "t_accounts", "t_funds"). However, how would you recommend I setup the relationships between the tables? Assume there are many employees to 1 account, many funds to 1 (employee + account).

I was thinking I could set it up 2 different ways:

Option 1

t_lookup:
lookup_id
employee_id
account_id
fund_id

The problem with this setup is I can't easily report on the employee to account relationship without using the "unique" feature of a query.

Option 2

t_lookup_1:
lookup_id_1
employee_id
account_id

t_lookup_2:
lookup_id_2
lookup_id_1
fund_id

I can now easily report on the individual relationships between employees and accounts, or employees, accounts and funds. However, the table construction is not that logical. It doesn't follow an intuitive setup.

Can someone please offer some recommendations?

 

[catalin.petrut]

My suggestion is to use the first option. And let me say why!
I see the access database like a software for users. You, like a developer, should create the easyest to use software.The query is some work for you, but will not be seen in the front-end of the application. So, use the first option becouse the database will be more logical. You don't need to make an extra table.
That is just my idea!
PS: or you can insert employee_id and acount_id in the found table. This is the most logical solution!

 

[Pat Hartman]

You haven't really described the relationships between the entities and that is what will dictate how you define the relationships.
I'm going to guess that an Employee may have many accounts and an account may contain many funds. So:
tblEmployee:
EmployeeID (autonumber PK)
FirstName
LastName
...
tblAccount:
AccountID (autonumber PK)
EmployeeID (long integer, FK to tblEmployee)
AccountName
...
tblFund:
FundID (autonumber, PK)
FundName
...
tblAccountFunds:
AccountFundsID (autonumber, PK)
AccountID (long integer FK to tblAccount)
FundID (long integer FK to tblFund)
Balance
.....

This is very simplistic and would not work in the real world where you have to track transactions but it gives you an idea of how the relationships work. Employee to Account is 1-many. Account to Fund is many-many since an account can contain many funds and the same fund might be held in many accounts.

 

[catalin.petrut]

@Pat, i think that there is a many to many relationship between employee and account. The one to many relationship is between emp+acc in the one side and found in many side.

 

[jdraw]

Tables and fields should have meaningful names, as Pat has shown. Also, for relationships, it isn't a guess -- it's based on your business rules/facts.

You should know from the business processes whether there is 1 Account per Employee or Many Accounts per Employee -- It shouldn't be a guess or flip of a coin.

If you have specifications, or if you try to write a description of the "business processes" your database is intended to support, you will start to see the things "Tables" and "Attributes" (Employees, Accounts, Funds....).

There is a good tutorial at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
that will help you with this table designing and relationships.

Good luck.

 

[Pat Hartman]

@catalin, I'm not sure I understand what you are saying. If the poster had used the term "customer" rather than "employee", I would have suggested a many-to-many relationship since in that context I would expect that an account might have more than one "owner". But in the context of an employee, it is unlikely that employees co-own accounts, hence the 1- many relationship. One employee, many accounts.

 

[catalin.petrut]

@Pat, i understand your idea. But, the poster said "many funds to 1 (employee + account)". Assuming that the account has only one employee, i guess he would've said "many funds to 1 account". The poster's info about funds relationship makes me think the many to many relationship betwen employee and accounts. That is why i assumed that.
PS: maybe we should let him (the poster) enlight us!

 

[milkman2500]

Hi,

Sorry for the delay and thanks for all the replies. I've been reading up on relationship modeling trying to learn more.

I'll add some additional context that might help the discussion:

There are many employees to many accounts. The accounts follow a yearly structure, so there is a 2010 account, a 2011 account and a 2012 account. So John may have a 2010 account, a 2011 account and a 2012 account. Each account has many employees, 2010 account has John, Adam and Jane.

There are many (employees + accounts) to many funds. So, John's 2010 account may have 5 different funds. But each of the 5 funds may also be in Jane's 2011 account or Adam's 2012 account.

I currently have it setup as follows, but not sure if it's the most efficient:

t_employees
employee_id
employee_name

t_accounts
account_id
account_name

t_lkp_employee_accounts
lkp_id
employee_id
account_id

t_funds
fund_id
fund_name

t_balances
lkp_id
fund_id
balance

 

[Pat Hartman]

Each account has many employees, 2010 account has John, Adam and Jane.

Are you certain that multiple employees can own the same account? What kind of account is it? Savings accounts, health care accounts, retirement accounts? None of these would be shared by multiple employees. Each would have his own account. If the account has a co-owner, the co-owner would be the spouse of the employee.

In your schema, funds are not related to accounts.