Relationships & Repeating Fields - Case 2

[bankmann]

Hi!

I've read elsewhere in this forum how to avoid this rather annoying problem.

However, following the advice not to use repeating fields with relations won't help me; that would mean creating a nightmarish mesh of up to or above a hundred extra fields. Yikes!

My problem relates to a couple of projects. I'll use the easiest one for example.

I have a multi part movie database, to keep account of my collection. The main db has (amongst others) 2 layouts with repeating fields linked to different related db-files.

One is a role/character and actor list;

(Rep.field Role/Character) (Rep.field Actor)

[Role/Character] [Actor]

[Role/Character] [Actor]

[Role/Character] [Actor]

[Role/Character] [Actor]

---

(and so on...)

The other is a crew/function list;

(Rep.field CrewFunction) (Rep.field CrewPerson)

[CrewFunction] [CrewPerson]

[CrewFunction] [CrewPerson]

[CrewFunction] [CrewPerson]

[CrewFunction] [CrewPerson]

---

(and so on...)

The clue here, is that I want to be able, under any given actor (in the actor db) or crew member (in the crew db) to list their 'achievements'.

The film shows up, no problem, but role/character or function shows up whatever it says in the first repetition; which is more or less almost always wrong, of course.

I know I could turn the repeating field strategy around a little, using the first repetition for Name and the next for Function (and so on), for example, but this would clash with the automatic value lists linked to some of these fields.

So what I really need is to be able to 'pair repetitions off'.

Like: [Actor_3_in_Repetition_10] pairs off with [Role_9_in_Repetition_10].

Does anybody have an idea how to do this?

Bankmann

[CobaltSky]

Hello Bankmann,

What you are trying to do is indeed nightmarish!

I hate to say it (because you've already said that you don't like the idea) but what you are describing is a textbook case for the use of a relational structure.

I also suspect that you are mistaken in thinking that going relational will require a whole lot of extra fields. On the contrary, a relational structure should eliminate all duplication, and seamlessly handle the linking of data elements which are all over the place in different repetitions in your current structure.

How it works is this.

In your main file, you have one record for each movie, with only the basic details about the movie itself.

Then in a related file called roles, you have a field which identifies the movie, another field which identifies the role and a third which identifies the actor. Every role gets a new record in the roles file.

From your movie file, you are then able to view a scrolling portal list of all the records in the role file which relate to that movie. The number of related 'role' records is unlimited, and they are all tied to the one record in your movie file via the movie name field (ie you have a relationship which links the movie name field in the movie file to the movie name field in the roles file, and that is used to source all the roles which relate to that movie.

Next, you have an actors file, which has one record per actor with all the background detail about that actor. Each time that actor appears in a record in the roles file, a relational link based on actor name will then enable you to source all the pertinent details about that actor. If the actor appears in twelve roles across ten different movies, details from the one actor record in the actors file will automatically be displayed in all twelve places, via the file relationships. Update an actor's birthdate in one place and the information is automatically and instantly available in every other place where that actor is mentioned throughout the entire database.

If required, an achievements file can link achievements to actors (each achievement being linked to a particular actor, and a list of all achievements for a given actor being accessible from the actors file - and via there from everywhere else the actor is mentioned).

The same simple structure (three or four fields per record, one record per movie crew member) will work just as well for your Crew data.

From the description you've given, changing to a relational structure will enable you to do away with a nightmarish maze of hundreds of (often redundant) field repetitions in favour of a disarmingly simple relational system with only a few fields to manage. In fact you should be able to do the whole thing with only a couple of dozen fields.

With this alternative approach, you will gain some other benefits, such as the ability to link from within your actors file back to the roles file, and instantly view a complete detailed list of all roles and movies for a given actor. Similarly, a link from the actors file to the crew file would enable you to instantly list every crew member an actor has worked with sorted by date, location, movie or any other data element available. A search in the achievements file will produce a list of all actors listed as having received a particular award. A dozen other possibilities of this type will be available if you want them - in a way you are never likely to achieve with a structure based on repeating fields.

Before you write my comments off as the wild rantings of a 'relational zealot', please do give it a try

[bankmann]

Hi, Ray!

Nope, I don't mind relations at all.

And as I read your posting, I realized you had a very good point.

In fact, as far as the movie database goes, you've probably solved my problem.

Appreciate it! ;-)

However, as my asset management project goes, things become a bit more complicated. And this is really where the field multiplication horror comes in.

The whole solution consists of several database files.

An interesting problem to solve, was how to build an dynamic/automatic IP configuration 'overview'. Especially since I needed machines to show in the correct subnet. (I also wanted to be able to do IP related calculations - although this hasn't been put in, yet.)

I accomplished this by splitting the IP address into its four octets; one per field; and just stringing the fields together on the layout. (I have calculations putting the address together in the 'background', as part of how figure out which machine belongs to which subnet.)

The problem is that some machines; most notably servers; often have more than one network card. Now, smart as I though I was, I made the octet fields repeating, to allow for NIC1, NIC2 (and so on).

And the same problem crops up; the machine will show in the right subnet, but the wrong IP address will be listed (The calculation that 'reassembles' the whole address is (as needs be) also repeating.), because the first repetition gets picked.

Which is why I was hoping there could be a way to make a caclulation that would 'link' two repetitions in different fields.

Bankmann

[CobaltSky]

Hi Bankmann,

Glad you coped so well with my suggestions about movies and relations

I think I see the issues you are having with the asset management system re the multiple IP addresses. I think there is a way to solve it which would involve calculating a concatenated field of IP addresses using a logica structure built around the Case( ) and GetRepitition( ) formulae.

However, I'm still not convinced that this would be the best way to go. I suspect that the alternative would be to create a related table of machines and IPs. Most Machines would have a single matching record in the IP table, and would not be affected substantially by the need to source the IP from the related table. Machines with multiple IPs, however, could then be flagged by use of the Count(IPtable::IPoctet1) function, and if you create a conditional value list in your main file, a summary of available IPs could be readily produced in a single field using the ValueListItems("MainAssetsFile.fp5", "RelatedIPs").

There are numerous other data management tools which would be available to you with the relational approach in this case (as with the movies example) which I suspect would be easier to implement and more flexible than the comparable approach based on logic applied to field repetitions.

More importantly, you should, with a relational approach, be able to create a subnet file which picks up all the machine IDs by IP from the IPtable, and then references the asset file for the rest of the details about each machine. If I understand correctly where you are trying to get to, this would give you 'multi-IP' machines showing up in each relevant subnet listing (with, if desired, a cross reference to the other subnets in which they also have a listing).

Hop you find these comments and suggestions helpful...