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!

 

[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.

 

[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