Jump to content

Reengineering relationships from FM6 to FM8


This topic is 6328 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have 20 related DBs in FM6 which I need to convert to FM8, joining them all into one file. The relationships b/w the different DBs in FM6 were established a variety of ways, usually by IDName, but in FM8 I know I need to create primary keys and foreign keys (arbitrary, auto-entered serial #s).

For example - Each person has several related titles, but each title record is only associated to one person. So, in Table:People I have Field:pk_PplID and Field:fk_TitleID and in Table:Titles I have Field:pk_TitleID and Field:fk_PplID. When I join these tables in FM8, by relating People:pk_PplID to Titles:fk_PplID, FM automatically creates a many-to-many relationship and doesn't work. Why?

"People" are tracked b/c they are in departments, on committees, are dead (don't ask), are related to the dead, are prize contestants/losers/winners, and have varying degrees of membership (based on title appointments) which allow or prevent their ingress into closed, confidential meetings. Each of the former DBs were set up to track these details.

All my data is already here, most of the fields I have are necessary and I've identified redundancies I have to eliminate. The relationships in FM6 are slapped together but work for my purposes, but I need to upgrade to FM8 so we can start hosting these DBs properly in FMServer. This first, fundamental step -- redefining relationships from those between multiple databases to those between table occurances in the same file -- is where I'm stuck. Any advice on how to start?

Link to comment
Share on other sites

FM8 will convert your files and retain the existing relationships and file structure as it stands.

This means you will get 20 separate files, as you already have, but working in FM8.

Resolving file references is the first think to tackle.

Have you tried a conversion run yet?

Link to comment
Share on other sites

Greetings lolita,

I see a one-to-many relationship between Person and Title (or Appointment) via the PersonID. Having a TitleID in Title is fine for relationships where Title is on the 'one' side of a relationship, but isn't involved where Title is on the 'many' side, as in this case.

This first, fundamental step -- redefining relationships from those between multiple databases to those between table occurances in the same file -- is where I'm stuck. Any advice on how to start?

Are you stuck on how to rebuild the tables in the single file, or on establishing the proper relationships?

Link to comment
Share on other sites

I see what you mean about the Titles. I didn't have the TitleID set as a unique serial number, which I've just changed, so now the relationship is showing as 1-to-many, wch is good.

The trouble is with establishing the relationships. The above example was just that - an example. I have lots more relationships that need to be rebuilt. I've had "people" in multiple DBs in FM6, but I think I can put them all together into one table in FM8. But each person can be in the DB for a variety of reasons: some may be there for one simple reason only (relative of dead person), and some can be there for lots of reasons (in many committees, voting member of the faculty, as a professor with a teaching appointment and simultaneously as a dean with an administrative role, in this or that department, etc.) I believe the way to bring all these files together is to put like data together (all the people in one table, all the committees -- be them admin, prize-related, judiciary, whatever -- in another, etc.). But I already have all the data -- I just need to reorganize/realign it, and the way to do that is by relationships, right?

Thanks!

Link to comment
Share on other sites

It sounds like you need to take some time to work out an ERD (Entity-Relationship Diagram). This is usually where large projects should begin. Once you have this roadmap figured out, working out the relationships in the graph is easier.

I'd suggest you research 'ER Diagrams', 'Data Modeling', 'Relational Theory', or something similar. This is a topic we've touched on in numerous threads (and even have a forum dedicated to it), so you may find useful information around here. Otherwise there are textbooks and websites that get into more of the general relational theory part of database design (the topic is pretty universal among different DBMSs).

When you understand what to look for, it will be clearer how to normalize your current structure.

If you want help from us with hammering out your ERD, we'll need to know much more detail about what you need to track and how they are related.

Link to comment
Share on other sites

Hey,

I'm working on my ERD for the 20 DBs, but in an effort to simplify, I'm trying this with two files which relate only to each other: PrizeData and PDAuxilliary. PrizeData tracks the 242 prizes we award here, and PDAuxilliary has the files of all the winners. I use a portal in PrizeData to see all the winners, sorted by the year they won.

In FM6, PrizeData (parent) had field:PrizeID, which I also created in PDAuxilliary (child), relating the two DBs together through those fields. The relationship worked fine. I converted both DBs into FM8 and all my data shows up great. Now I'm trying to insert primary and foreign keys, and redefine the relationship through those. So, in Table:PrizeData, I created primary key field kp_PrizeID (as serial number, unique, can't modify) and kf_PDAuxID, and in Table:PDAuxilliary I created kp_PDAuxID and kf_PrizeID. What do I do now? If I just create the relationship -- linking kp_PrizeID in Table:PrizeData to kf_PrizeID in Table: PDAuxilliary, and remove the one linked by the original fields (which are really unique code abbreviations of the formal name of the prize), my portal shows nothing.

I filled in the values in the new primary keys for both tables (and set FM to serialize the next new records from this point forward). I think I need to populate the foreign key in my extant records, tho, but how do I do that without doing it one at a time? I think this is where I'm stuck.

Thanks for any advice you can offer.

Link to comment
Share on other sites

Gotcha. Yeah, you need to populate those foreign keys. This can probably done from your old relationship setup, using:

Replace Field Contents [ fk_PrizeID; PrizeData::fp_PrizeID ]

Be sure to make a backup before trying this, as there's no undo with a Replace. Once the new keys are populated, you can switch the relationships to use them.

Link to comment
Share on other sites

This topic is 6328 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.