Question about normalizing tables

[pld60]

I have a table with the following fields,

ID (primaryKey)
Date_Created
Date_of_Event
Time_of_Event
Number_Staff_Needed
Staff1
Staff2
Staff3

that keeps track of events that 1 to 3 staff may be involved in.

My question is does this violate the 1st normal rule of no repeating groups. If it does should I have a table for each Staff?

This is table is filled from a form with the staff names entered in a comboBox That gets the names from a table with all of the staff names.

 

[plog]

I refuse to learn unnecessary jargon, so I can not accurately classify your error as to the normal form it violates. I can however explain the error (actually errors) and tell you how to correct them.

When you start to numerate field names (Staff1, Staff2, Staff3, etc.) its time for a new table. That table would hold a foreign key to your existing table (ID), then 1 field for every numerated set, and then possibly another field if the number portion of the field name is relevant. That means this would be your new table:

Staff
staff_ID, autonumber primary key,
ID_ExistingTable, number, foreign key to the ID field in the unnamed table you posted
staff_Member, same data type as Staff1, Staff2, Staff3, will hold data that goes in those fields
staff_Number, number, might hold numeric portion of Staff1, Staff2, Staff3

That's it. Not multiple tables, just 1 and that will hold all your staff data for all your records. If a record has 3 Staff values (Staff1, Staff2, Staff3), it would become 3 records in the Staff table. Like I mentioned before staff_Number may or may not be necessary. Does the '1' portion of 'Staff1' actually mean something? A person listed in Staff1 isn't signficantly different from someone in Staff2 or Staff3, correct? That was just a way to create distinct field names? IF so, staff_Number isn't needed. If it designates something (priority, different role, etc.) then it might be needed

Also, is 'Number_of_Staff_Needed' redundant/calculable? That value will always equal the number of values in the STaff1, STaff2 & Staff3 fields, right? If so, that field isn't needed, you just have the computer fiugure it out in a query.

Lastly, Date_of_Event & Time_of_Event should be combined. Its a Date/Time field for a reason--it can hold both components. Storing them in the same field will make life easer down the road.

 

[MarkK]

Yes, you need a new table called tEventStaff with a structure like . . .

tEventStaff
EventStaffID (Primary Key)
EventID (Foreign Key)
StaffID (FK)

This will show you what staff attend an event AND what events a staff attended.

If you make a subform on this table, you can use the same subform on both the Event AND Staff main forms.

 

[gemma-the-husky]

I agree with plog about the "learning" of normalised types

What I would say, is that I sometimes deliberately break normalisation, as managing a sub-table can be a bit of a pain, but it is a conscious decision I make.

eg, in your case
... what if you ever want more than 3 staff members?
... what if you want to find staff involved in several projects. Now you need to query on staff1, staff2, staff3, and combine the results.

If you don't envisage either of these, then it may be easier to do what you are doing.

fwiw, I just re-read notes on normalisation.

Storing the staff members as multi values

ProjectID: 1
Staff Members: "John,Jim,Fred" is clearly not normalised (although MS have recently added a database feature to manage just this situation)

but
ProjectID: 1
Staff Member1 "John"
Staff Member2: "Jim"
Staff Member3: "Fred"

is either normalised or not, depending on how you want to regard the repeating staff members.

----
In the same way, a person database may well store phone numbers horizontally, rather than vertically, home, mobile, work etc. I think that's what my mobile phone does anyway.

 

[Uncle Gizmo]

In the same way, a person database may well store phone numbers horizontally, rather than vertically, home, mobile, work etc. I think that's what my mobile phone does anyway.

Dave's comment reminded me of my video blog explaining exactly that, whether to use a vertical or horizontal table structure for phone numbers. Blog Excel in Access here:-

https://sites.google.com/site/msacc...-access-parts-1-2-and-3-as-video-instructions