I'm trying to get my head around the concept of using join table
appropriately.

Let's say I have a film database and want to include cast lists so that
I can list films with the cast displayed under the title -- and also
create an alphabetical index of cast names with the films they appear in.

So, (A) I can make a "Cast" table, slap a portal on my main 'Film' Table
layout, and spew out records in the table, one record per cast
appearance. Right?

Okay, so now I'm thinking, what if I want to be more detailed about the
actors? Male or Female, nationality, perhaps an image of them, Bio info,
etc. So (B) I make a 'Cast' table that devotes one record to each actor
with all the fields I want.

Now how do I tie the actors to the films? THis is where a Join Table
comes in. (B2) I make an "Appearance" Table, with foreign keys linking
to the primary keys in the 'Film' and 'Cast' tables. Here each record
represents a single appearance of an actor in a film. ANd in this table
I could place fields about that appearance -- for instance, the
character name (Role), how many kisses they got, etc. (Then I could
tally who got most kisses over a career!).

So . . . am I thinking properly about this concept?

Am I right that the the single table approach (A) is sufficient /
effective if I want simple 1-field indexes of information? (For
instance, if I want to index keyword 'subject' words like baseball,
boats, Boston, etc.?). That seems right to me -- I don't see any benefit
makign a table listing all these terms and then a join table noting
appearances, since that wouldn't add any value or save any labor --
would it?

Thanks!

Albert

In article <asteg-0BD445.16590131052007 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

So far so good. You seem to have the concept:

3 tables: Film, Actor, Appearance

Key Field: FilmID, ActorID

Relationships:

Film::kpFilemID = Appearance::kfFilmID
Actor::kpActorID = Appearance::kfActorID

Details of the Film are in the Film table, details of the Actor in the
Actor table, detail of the appearance in the Appearance table.

From this setup you can generate lists of Appearances showing Film &
Actor; lists of Films showing Actors, and lists of Actors showing Film,
all in various combinations.

I don't know what you are getting at here. Sounds like maybe you are
talking about a value list of items for certain fields?

--
For email, change <fake> to <earthlink>
Bill Collins

In article
<bbcollins-D90230.17373631052007 (AT) customer-201-125-217-207 (DOT) uninet.net.mx>
,
Bill <bbcollins (AT) fake (DOT) net> wrote:
Great -- Now, last question: how do I best manage the portals for data
entry? I'm thinking a portal on my 'Film' page leading to the
'Appearance' Table that would generate records there as I type in the
cast list . . . but do I put a field from the Actor table in this portal
as well, with a 'Value List From Field' -- the field being the Actors
name? Or am I supposed to create a Layout based on the Appearance table
where I enter the foreign key values by use of this sort of Value List?

I'm just trying to get a feel for when it's *not* worthwhile to make use
of a join table. In a way, thematic keywords are like actors -- they
'appear' in some films but not in others. But since there wouldn't be
any more detail 'about' the appearance of a theme in a film other than
it's just being there, there would be no need for a join field. Right?

thx
Albert

In article <asteg-0F6C29.06391501062007 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

The following will work:

In Film layout, put a portal to Appearance.
In the portal row, put the field Appearance::kfActorID
Also put the field Actor::Name

Format the field for kfActorID in the portal row to display a dynamic
value list, taken from the field Actor::kpActorID and second field
Actor::Name. Define the value list to display only the second field.
Format the field to display the value list as a drop-down list.

Now when you click in the field Appearance::kfActorID, you will get a
list of actor names. Pick from the list. That will assign the
corresponding value of Actor::kpActorID to the field
Appearance::kfActorID, and thus assign that actor to that appearance. he
name of the assigned actor will now appear in the field Actor::Name in
the portal.

If you have more than one field for Actor Name in the table Actor, for
example NameFirst, NameLast and NameSuffix, you would define a
calculation field in the Actor table to concatenate those, thus:

NameLastFirstSuffix = Trim(NameLast & ", " & NameFirst & " " &
NameSuffix)

Then you would use that calculation field as the second field in the
value list for actors.

Such a calculation field can be used in a value list if all the fields
belong to the same table as the first field in the value list
definition. It will not work if any of the fields belong to another
table.

You could of course also do the inverse of this setup: Put a portal in a
layout of Actor, to Appearance, include the field kfFimlID, and format
that as a drop-down list of films, first field Film::kpFilmID, second
field Film::Name


This is a matter of judgement in the specific case.

It is usually good enough to just use a value list for such attributes,
rather than a separate table of attributes and a join table. You can
even use the value list to assign multiple attributes per film, by use
of a checkbox set.

However, if you want to be able to assign multiple attributes to the
same film, and then want to do a report that counts the number of
occurrences of each attribute, you would be better off using a join
table setup. If you only have one attribute per film, the simple value
list approach works fine.

--
For email, change <fake> to <earthlink>
Bill Collins

All good on the Join field tips -- thanks very much Bill!

As to the Value list . . .

Bill <bbcollins (AT) fake (DOT) net> wrote:

Clearly, if such a field has only one value per record, a field on the
main layout with an appropriate value list will suffice. . . but I'm not
seeing why, for 'multiple attributes' such as subject keywords, a simple
"one-to-many" relationship to a "Subject Index" table wouldn't do the
job. If I have a portal that spits out multiple keyword records related
to the main Film record by a keyfield, won't that allow me to "count the
number of occurrences per attribute'? -- I have this working in another
database I made, where I use sub-summary parts and sorting to create a
keyword index -- an alphabetical listing of the keywords, with the
related films listed beneath them. Wait -- is the difference that in
order to view this index, I have to go into Preview mode? That just
occurred to me!

This is the question I'm asking -- this approach seems functional, but
I'm not confident that I'm not limiting my possbilities somehow.

Thanks for your patience and help.
Albert

In article <asteg-4AD7A4.18012901062007 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

Do you want one attribute to apply to many items, or one item to have
many attributes, or any number of items to have any number of attributes?

If one item can have many attributes and one attribute can apply to many
items, that is by definition a many-to-many relationship, which requires
a join table, thus:

Item::kpItemID = Item_Attribute::kfItemID
Attribute::kpAttributeID = Item_Attribute::kfAttributeID

No other way to have one item with many attributes and one attribute to
apply to many items, in a way that allows population analysis. You can
use a multi-value field with a checkbox set, but that does not allow
easy population analysis. A one-to many relationship won't do the job
either, it is logically insufficient.

--
For email, change <fake> to <earthlink>
Bill Collins

"Albert" <asteg (AT) mindspring (DOT) com> schreef in bericht
news:asteg-4AD7A4.18012901062007 (AT) news (DOT) west.earthlink.net...
Properties or rather attributes should not reside in a different file. But
the difficult part might be just to decide wether the data involved is an
object or an attribute. Generally with things as sex, age you can safely
assume they are attributes and belong in all the same record. With things as
addresses it might get complicated. For a single person might have a house
address, a company address etc. Then the address becomes an object instead
of an attribute and should belong in a separate table. Try to read more
about objects and attributes, there is a fair amount of info on this on the
web, related to databases.

Keep well, ursus

Thanks Bill, Ursus -- I think I've got a grip on it now.

Best,
albert

On Jun 1, 8:45 am, Bill <bbcoll... (AT) fake (DOT) net> wrote:
I'm a bit confused about your solution. I'm trying to do something
similar. What happens when you have more than one actor for the film?
It sounds like the actor display field that you put in the portal
would only show one at a time....Also, what if you want to remove an
actor from the film?

In article <1181919760.886653.275700 (AT) w5g2000hsg (DOT) googlegroups.com>,
Dharma.Akmon (AT) gmail (DOT) com wrote:

Three tables:
Actor
Film
Actor_Film

Each record of Actor_Film joins one actor with one film.
You can have many records in Actor_Film.
Any film can have many records in Actor_Film, and hence many actors
joined to it.
Any actor can have many records in Actor_Film, and thus be joined to
many films.
Hence, the relationship is called "many-to-many."

Actor would have a field for a unique identifier for each actor,
normally a serial number generated automatically when the record is
created, called kpActorID.
Film would have a field for a unique identifier for each film, normally
a serial number generated automatically when the record is creates,
called kpFilmID.
Actor_Film would have a field kfActorID and another field kfFilmID.

Records of the three tables are joined by these two relationships:

Actor::kpActorID = Actor_Film::kfActorID
Film::kpFilmID = Actor_Film::fkFilmID

Where the notation Actor::kpActorID signifies the field kpActorID that
is part of the Actor table, and so on.

The table Actor would have other fields to store information about the
actor, such as name, birthdate, etc.

The table Film would have other fields to store info about the Film,
such as name, genre, date of release, etc.

The table Actor_Film would have other fields about the assignment of
that actor to that film, such as role, name of character, and anything
else peculiar to the assignment of that actor to that film.

This setup allows you to assign many actors to a film, with all the
detail you could want about each, and conversely to assign many films to
one actor.

--
For email, change <fake> to <earthlink>
Bill Collins