Question about Proper Table Structure

[Stang70Fastback]

So I know you guys on here like to harp on everyone about proper database design and structure, so I'm sure there is someone who would love to answer this question.

I have a table which holds records for an employee's work day. As it stands right now, along with all of the other information, there are 5 fields (Duty 1, Duty 2, etc...) along with 5 corresponding fields for other information (Hours Worked 1, Overtime 1, etc...) All of these are in 1 record.

I started working on a report design that would tell us how many hours of each type of Duty would work (the Duty category is a drop-down.) I'm sure I can extract the relevant data from each record, but I'm starting to wonder whether the "proper" way to design the table would be so that each Duty is entered as its own record. So rather than the instructor having ONE record listing multiple duties for each day, they might have multiple daily records if they did more than one thing. However then you start getting into duplicating the other data, so now I'm thinking maybe I need two tables with some sort of (one to many?) relationship... or maybe the way I had it set up originally was okay.

Does anyone have any insight on this? I don't want to get too far on building things out if I'm going to have to go back and change the basic structure. Is what I'm doing currently (listing multiple tasks in one record that I might want to query separately) the completely wrong way to do it? (Probably.)

 

[plog]

Whenever you start numerating Field Names (Duty1, Duty2, Duty3...) its time for a new table. So you yes, you need more tables.

If Duty1 and Hours Worked 1 and all the other '1' suffixed fields go together, then they all go into the same record in the same table. If however, Hours Worked 1 isn't related to Duty1 or anything else, it goes into its own table as its own record.

For specific guidance I'd need to see sample data. First, though read up on normalization (http://en.wikipedia.org/wiki/Database_normalization) and give it a shot yourself. Post back here your relationship view.

 

[RainLover]

Mr FastBack

You have been around for a while so I would think that you have a basic idea of what to do but just not 100% sure.

I have found that sometimes you have to break normalisation rules to be able to write something that works.. A TMS (Time management system) is often one of these cases.

Do you have something in particular that is bothering you with this design.

 

[ButtonMoon]

Stang,

I think your instinct is right. I would expect to create a table for duties, with columns for EmployeeId, Duty, HoursWorked, Overtime. That table would have one row per employee/duty. Normalization doesn't mean you have to eliminate information that is "duplicated". What it means is that the functional dependencies in your data model are represented accurately and properly enforced by keys. Functional dependencies are business rules that your model is supposed to satisfy.

As a rule, aim for your database designs to satisfy at least Boyce Codd Normal Form (BCNF) and generally Fifth Normal Form. The main reason to deviate from that is if you have some business rules that can't be represented accurately in a BCNF data model - probably unlikely in your timesheet scenario.

 

[Stang70Fastback]

Thanks for the help guys. So this is what I've come up with so far. I think I'm headed in the right direction, but I've still got a few issues that I need to work out.

I've split my tables as such:

Table1
-Employee Badge
-Date
-Some other fields about the current day
-Remarks

Table2
-Employee Badge
-Date
-Task
-Activity
-Overtime
-Hours

So basically I have an overall record that gets created for the day's activities (Table 1), and then a list of the individual activities themselves (Table 2). My layout a Main Form with Table 1 as the record source, and then a Continuous Subform with Table 2 as the record source.

My issue now becomes relationships. I am indeed not new to Access, but I've actually never really dealt much with relationships. The way the Subform is currently configured, is that the linked master fields and child fields are "Badge;TheDate" which makes sense. However, it looks like this really is only a filter of sorts. It is displaying only those records whose Badge and Date match the Badge and Date on the main form. The records aren't actually really LINKED together.

So if I delete the record on the main form, the subform records disappear, but they aren't actually deleted. I realize at this point I need to build an actual relationship using the relationships tool, but I'm not sure how to build a relationship based on TWO fields (Badge AND Date.) How can I go about this? I thought about using the RecordID created in Table 1, and pulling it into each associated record in Table 2 (RelatedID, for example) so that I could create a one-to-many, cascade delete relationship (RecordID -> RelatedID), but I just feel like that's the "wrong," or "cheating," way to do it.

EDIT: I also looked over the rules of normalization. I *think* my tables are 1,2,3NF compatible, with the exception of the date field. I feel as though maybe I should only be storing that in one or the other table but that's if I use only one relationship (the badge, for example) instead of two relationships (badge and date.)

 

[jdraw]

Just curious -- does your report(s) include EmployeeName, Address, Position?? or just BadgeNumber??
It seems to me you are addressing part of an environment, which may be perfect for your needs. However, if you have to relate this info to Employee, Pay, Benefits, it seems you may have some inter-system relationship or reconciliation to do.Again readers do not know your environment.

Date is a reserved word in Access. Suggest you use something like WorkDate or ActivityDate....

Good luck with your project.

 

[Stang70Fastback]

This is indeed part of a much larger database I am building to track all sorts of things.

The 'Badge' field is a drop-down which is populated from an "EMPLOYEES" table which has First Name, Last Name, Work Location, etc...

And don't worry, the field names I listed were just representative to make things easier to read (maybe that was a bad idea.) The field name is actually 'TheDate'.

As a good example, though, I know how to define a relationship between the employee badge, and the EMPLOYEES table, which is just linking the two badge fields together in a one to many relationship. But I'm not sure how best to approach my issue with Table 1 and Table 2, where one field alone isn't enough to properly define the relationship (at least not the way I am doing it.) I need the badge and the specific date of interest to have the records properly attached.

 

[jdraw]

It might help readers if you assigned or told us the names of table1 and table2 in your environment.

If circumstances warrant you can have
- a compound PK, or
- a surrogate PK and then a compound unique index on the fields required to make a record unique within the table.

Also, I would think a Task (or Activity) have a StartDate and EndDate/FinishDate, so I'm unclear on exactly TheDate represents.

If you have a data model of the entire, larger database then a jpg of same would help put many things into context.

Good luck with your project.

 

[RainLover]

As a good example, though, I know how to define a relationship between the employee badge, and the EMPLOYEES table, which is just linking the two badge fields together in a one to many relationship. But I'm not sure how best to approach my issue with Table 1 and Table 2, where one field alone isn't enough to properly define the relationship (at least not the way I am doing it.) I need the badge and the specific date of interest to have the records properly attached.

If you used a Auto Number then that would work. One Field as Primary key has got to be easier to manage than two or three of four. Do you have a problem with this. Can you see how this could possibility fail to deliver what you want. If so then what jdraw also suggested which is the same as what I have only it provides for other circumstances.

The following part of what jdraw suggested is the way to go. The part in Bold.

If circumstances warrant you can have
- a compound PK, or
- a surrogate PK and then a compound unique index on the fields required to make a record unique within the table.

- a surrogate PK and then a compound unique index as may be required in some tables.

I think that this is the basics for solving all the problems you could have when referring to primary keys.

The only other thing you need is a good naming convention to keep things standard.

 

[Stang70Fastback]

Sorry guys. I got roped into ANOTHER Access project for the past two weeks. Just wanted to come back and say thanks! In all my research, I never happened to stumble across the term compound primary key (or composite as they call it, apparently.) This was all I needed to figure things out. Thanks again!