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]

Re: normalizing help

What value is contained in tblEVENTDATA.Ev entSiteLeader?

[Kevin]

Re: normalizing help

On Sat, 21 Jan 2006 19:58:15 -0800, Jamey wrote:
[color=blue]
> What value is contained in tblEVENTDATA.Ev entSiteLeader?[/color]

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

[Jamey]

Re: normalizing help

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.Ev entRole field, it may be
useful just to eliminate EventSiteLeader and EventGroupLeade r 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.Ev entTypeID and
tblEvents.Event Type). Hopefully, you aren't storing the
tblEventType.Ev entType data in the tblEvents.Event Type 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]

Re: normalizing help

On Sat, 21 Jan 2006 20:35:15 -0800, Jamey wrote:
[color=blue]
> 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.[/color]

Thanks for your suggestion!
[color=blue]
>
> If you have similar data in the tblEventData.Ev entRole field, it may be
> useful just to eliminate EventSiteLeader and EventGroupLeade r 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.
>[/color]

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.

[color=blue]
> Looking over your table layout, you may want to standardize the field
> names in related fields (i.e. tblEventType.Ev entTypeID and
> tblEvents.Event Type). Hopefully, you aren't storing the
> tblEventType.Ev entType data in the tblEvents.Event Type 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.[/color]

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]

Re: normalizing help

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.