normalizing help

[Kevin]

I'm almost done working on my relationships and the database groundwork
- I'm stuck on one part.

I have the relationships and tables as shown here:
http://home.hawaii.rr.com/kevlinux/o...ationships.jpg

Now, notice the tblSITELEADERS off to the side. Some contacts are site
leaders, but they're not all the time. I'm trying to figure out best how
to handle them. I had a 1-~ relationship from ContactID to ContactID, but
then realized there's no way to keep track of what events they were site
leaders, and which they weren't.

So, now I'm thinking add a Yes/No to tblEVENTDATA called SiteLeader, and
keep track that way, while only keeping Site Leader data for those
contacts who are site leaders at some point. I could also get rid of one
of the "ID" fields in tblSITELEADER and just make a 1-1 relationship
between it and tblCONTACTS, but then ALL contacts get an entry.

Opinions?

[Jamey]

What value is contained in tblEVENTDATA.EventSiteLeader?

[Kevin]

On Sat, 21 Jan 2006 19:58:15 -0800, Jamey wrote:

What value is contained in tblEVENTDATA.EventSiteLeader?

That's the Yes/No field I was talking about.

[Jamey]

Seems like that'll work. You won't need the SiteLeaderID and ContactID
in the SiteLeader table if those two fields contain the same data. I'd
just go with the 1:1 relationship for ContactID in the Contacts and
SiteLeader tables and only track the data as applicable.

If you have similar data in the tblEventData.EventRole field, it may be
useful just to eliminate EventSiteLeader and EventGroupLeader from the
table. That way you'll be tracking those roles with Byte data or
Integer data vice Boolean data, but you could possibly combine 3 fields
to 1 with something like that. I may be misinterpreting EventRole, so
that may not even be something you can do.

Looking over your table layout, you may want to standardize the field
names in related fields (i.e. tblEventType.EventTypeID and
tblEvents.EventType). Hopefully, you aren't storing the
tblEventType.EventType data in the tblEvents.EventType field. If you
are, it may be more human readable in the tables, but you should never
be inputting data directly to tables anyway, and the ID fields will be
more useful in SQL because they are unique IDs on the 1 side of the 1:M
relationship.

[Kevin]

On Sat, 21 Jan 2006 20:35:15 -0800, Jamey wrote:

Seems like that'll work. You won't need the SiteLeaderID and ContactID in
the SiteLeader table if those two fields contain the same data. I'd just
go with the 1:1 relationship for ContactID in the Contacts and SiteLeader
tables and only track the data as applicable.
Thanks for your suggestion!

If you have similar data in the tblEventData.EventRole field, it may be
useful just to eliminate EventSiteLeader and EventGroupLeader from the
table. That way you'll be tracking those roles with Byte data or Integer
data vice Boolean data, but you could possibly combine 3 fields to 1 with
something like that. I may be misinterpreting EventRole, so that may not
even be something you can do.

DUH! *smacks forehead* The EventRole field is actually a field pointing to
a ContactType, which is "Site Leader" or some other value. That would
negate the need for a SiteLeader Yes/No field. The GroupLeader is a
Yes/No field telling whether or not the person is a group leader for a
group of people - different from Site Leader. I could probably just add a
field in ContactType for "Group Leader", like I think you're
suggesting, but then if they're a group lead and a site lead, it could get
tricky.

Looking over your table layout, you may want to standardize the field
names in related fields (i.e. tblEventType.EventTypeID and
tblEvents.EventType). Hopefully, you aren't storing the
tblEventType.EventType data in the tblEvents.EventType field. If you
are, it may be more human readable in the tables, but you should never
be inputting data directly to tables anyway, and the ID fields will be
more useful in SQL because they are unique IDs on the 1 side of the 1:M
relationship.

Yes, I need to standardize the naming. As for how I'm storing, everything
I can have in INT data is in INT data as far as I can get it. I'm not
worried about readability in table view, just being able to query the
values and do it right. ;)

Thanks for the pointers,

Kevin

[Jamey]

Agree the SiteLeader AND GroupLeader role might get tricky if you ever
have a SiteLead who isn't GroupLead. Don't know if that happens, but it
seems pretty plausible that it does. If that's the case, you've got a
whole different can o' worms that might wind up having a solution like
a GroupsByEvent table, in which case you're fast approaching the
slippery slope of Composite Key indexing vice Primary Key indexing, and
Access doesn't have as good of faculties for handling those.