Creating Relationships: Is this a reasonable Relationship?

[RootMason]

Hello all!



I am trying to create a new time & expenses database for a scientific company. I have created a database based on the old information (tables) they had:
Employees
Time
Expense
ExpenseDescription
ProjectInfo

I have create a database that I believe will function correctly together, but I would like a review from you big brains on my idea to create Relationships between the Tables. Here is my design:


Here is my logic on this:
Every Employee has many Times; Every Time has ProjectInfo
Every Employee has many Expenses; Every Expense needs ProjectInfo; Every Expense needs ExpenseDescription

So, my question is: are these reasonable Relationships?

I am especially unsure about how to feed ProjectInfo & ExpenseDescription into Expense. The idea being that an employee will use a form to sign in, select to enter Time or Expense. If they enter expense, they must choose from two dropdown column menus with ProjectCode & ExpenseDescription.

Thanks much everyone, sure do appreciate the time!

Steve Clark

[RootMason]

My apologies, by "ProjectInfo" above, I meant to say "ProjectCode". Thanks!

[A S MANN]

The above table can be replaced by
1: Project
2:Employee
3:Expenses
Project and Employee has many to many Relationship. The intermediate table can be Expenses

Project (One----Many ) Expenses (Many -----One) Employee
Time is part of Expenses
Pl thinks. This will simply

[ItsMe]

I recommend deleting all relationships within the relationships window and drawing your proposed relationships out on paper. I also recommend place "FK" on the trailing end of each field name that represents a foreign key Field/Column. Drag your FK's to the beginning of your columns, near the PK, so they are grouped together and easy to identify.

I rely on Access query objects and SQL statements within code or control properties for my relationships. Why define a relationship in the relationship window that may or may not be trumped by a Join via SQL?

[RootMason]

Thanks all for the comments!

A S Mann, I think I understand with the intermediate table. Would the same be true as well for Project (one - many) Time (many - one)Employee?

ItsMe, thanks again for all the advice and I am trying to understand how relationships work exactly. I did sketch it out on paper and this is what I came out with, so clearly I have more work to do on understanding how they are joined. Any advice?

[ItsMe]

Delete the relationships in the relationships window. Use the query builder to define your relationships. Enforce referential integrity with VBA and action queries. Work on one function of the database at a time. Try concentrating on expenses first, for instance.

[A S MANN]

First Decide on Tables. Create a primary Key in each Table. Than decide How these are related One--One , One---Many, Many---Many etc. Use relationship diagram to create relationship. Drag Many Foreign Field to Primay field & drop. It will create relationship.

[RootMason]

My apologies for not responding sooner, Christmas takes all the time I've got! And Merry Christmas All!

ItsMe, I reread the book I got on MS Access Databases (MS Access Plain & Simple) and I think I understand better what you mean by removing the relationships and using queries instead. What I am confused about here is: what are the relationships for then? I did notice how they seem to be unnecessary this time through, but I am sure they have their use and I'd like to understand what that is.

A S MANN, I have a basic understanding of how to create the relationships, it's what I did to create the original relationships above. I feel that the flow of it is all wrong though.

What I am trying to accomplish here is to have simple, web-accessible (via Sharepoint) forms for employee timesheets, employee expenses, company projects and a way to report this to accounting.

Here's what I'm thinking I need for employee expenses (please let me know what you think):

A table for Employees, as one Employee will have many Expenses entries
A table for ExpenseDescriptions, as each ExpenseDescription will have many Expenses entries
A table for CompanyProjects, as each CompanyProject will have many Expenses entries
So the Expenses table will be a Transitional Table, taking dropdown data from a form employees fill out (with Employee, ExpenseDescription & CompanyProject data) and feed it to the Expenses table.

Also, one Employee will have many Timesheet entries
And one CompanyProject will have many Timesheet entries
So the Timesheet table will be a Transitional Table, using the same method above.

One thing that wasn't covered clearly in what I read is how exactly I configure these Transitional Tables (if I'm even using that term correctly).

So, that's where I am at the moment. My schedule is much clearer for a while, so I will be much quicker to respond. I'd love to hear what you guys think, any advice is much appreciated! Thanks again for being there to help us all learn!

Steve Clark

[ItsMe]

...What I am confused about here is: what are the relationships for then? I did notice how they seem to be unnecessary this time through, but I am sure they have their use and I'd like to understand what that is...

It is not so much the question, "What are relationships for?" The comment I made is about the relationships window. So the question is more like, "What does the relationship window offer?"

I perceive the relationships window as a tool to manage the model that is your application. In the spirit of "Rapid Application Development", Access offers this window to speed things up when creating the application. You can create referential integrity rules that will cause cascading deletes when a record is deleted from a parent table. You can also define a join in the relationships window that will automatically appear when you use the query builder. There are also other features. The idea is you do not have to take into consideration the "Model" while building your application. The relationships window will perform black box actions on your behalf.

I prefer not to use the relationships window because I believe the code I write should dictate how the application performs. I will create functions or use built in functions to enforce the referential integrity of my relations. The act of building a query creates a join that defines a relationship. If a relative record needs to be created, I will use VBA to get it done. I prefer to remember the model than to remember how the relationship window interprets the model.

It is not a bad thing to use the relationships window. many people will use it religiously and use it in an effective way. When I use it, I get the feeling the walls are closing in on me and I get anxious. I feel that I have better control when I create my model outside of Access. There are standards for such modeling. One common standard is the Universal Modeling Language.

UML is not a simple thing but the idea is that you engineer your application using symbols. You can draw symbols on a piece of paper that you and others can reference while developing and collaborating. You would not have to use UML to create a meaningful model. You can create your own blueprint much like you would create a map to the Christmas Party. Just be consistent with your symbols and don't get too creative. The saved query objects can assist by offering illustrations too.

[RootMason]

I think I'm a bit fuzzy on what exactly you mean by a UML. Basically, you are talking about a drawn diagram of how the different pieces fit together, correct? If so, then I have, and I'd be more than happy to scan it and post it here, but it looks a lot like the relationship window that I put together in the first post.

If I'm wrong about that, my apologies, maybe you could point me in the direction of somewhere I could learn more about it. As is, I'm new enough that I don't think I have too much to fear from feeling like the walls are closing in on me with the relationship window. If it's an easier way of accomplishing the same task then I'd prefer to use that since I'm still learning... and since I'm doing all this technically off-the-clock, as I'll only get paid for the hours [more like weeks] of work if I can show something substantially better than their current system.

Thanks again for all your input!

Steve

[ItsMe]

I mentioned UML only to illustrate there are standards for creating professional models and it is a common practice to draw out what it is you are planning. I also mention it because there are several software programs that attempt to automate and simplify the process of creating and managing the model. It seems the relationships window is Access' answer to creating an engineering model or blueprint for Access programs.

I choose to avoid using the relationships window because I feel it may, at times, limit my flexibility during development or cause things to happen while the program is running (referred to as runtime) that I did not take into account. You do not need to understand UML to create your application. Simply understanding it is there may provide insight how to continue.
http://www.tutorialspoint.com/uml/uml_quick_guide.htm

I believe that a model you create on paper should look a lot like the relationships window. How the relationships window illustrates the relationships is beneficial. However, query objects can provide the same illustration and you can manage referential integrity in class modules associated with forms. Your notes (model on paper) will not conflict with how you write VBA in a class module and your notes will not try to dictate how you build your query objects by magically intervening while you add tables and queries to the query builder window.

[RootMason]

I'll take a look at the UML link in a while, thanks much for the direction!

So moving forward, are the tables I want to use reasonable, do you think?

[ItsMe]

typically names of tables for customers, employees, vendors, etc. are plural - tblEmployees

I like to distinguish my foreign key field names from names that are primary keys. So maybe, EmployeeID and EmployeeFK

You need to avoid words that are reserved by Access. Naming your table "Time" will cause hurdles at the very least. If it sounds very intuitive, it is probably reserved. So tblTime and tblExpense. I usually try to combine two words together to give further distinction. It goes against the need to have short names but it is nice when you keep adding more and more to your DB. So tblEmpExp and tblEmpTime. This further reduces the risk of reserved words and also keeps the name intuitive.

Your relationships should be based on long integer values. You are joining your tables on everything except the PK to FK. When you open your event tables they should be populated with mostly numbers. These numbers should be foreign keys that represent the relative record in another table. If the table can be read and makes sense, it is probably wrong. It should be cryptic until after you create a query and use the joins.

As I mentioned before, break off small pieces. Define a task. You have defined two. Now you have to make everything work for two event tables. I would isolate the time table and determine how accounting interacts with the current DB. Create queries that provide info relative to the time events. Create forms that users can enter their times into.

If an employee can work on multiple projects in a given day or time period, you will want to store the project ID in the time table. In a form, offer a combobox to the user so they can select an available project. Worry about how the project table is maintained and its matrix at a later date.

By isolating an event, one at a time, you will better understand the relations of other tables and event type tables. Perhaps a junction table is needed. Decide these things after you have a working model for one of your events. Fix your PK to FK relations.

[RootMason]

Thanks, I appreciate the input, and that makes a lot of sense.

I will modify the names of the tables (I've already run into problems with naming conventions for columns). I'm about to try another mock-up of this (probably tomorrow) using a lot of what you and others here have mentioned, I'll post more tomorrow on what I manage.

[ItsMe]

This thread has some good info and links for naming conventions
https://www.accessforums.net/access/...ons-39212.html