Jump to content

Keep Data in Table After Relationship Change


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

Recommended Posts

Hello, I have a 'data retention' issue that has been driving me absolutely insane over the past two days. It is actually a multi-part issue, but rectifying the issue in this post may solve the others (so I'll keep it simple... for now).

In an nutshell, I have 3 tables related by a locationID.

Location
locationID

Equipment
equipmentID
serialnumber
fk_locationID

FLIGHT
flightID
fk_locationID
fk_equipmentID

The FLIGHT table is the main data storage entity; Location and Equipment tables just provide data to fill FLIGHT table.

While Equipment stores the current location of a piece of equipment, FLIGHT also stores the location of the current flight (the flight's origin).

There is an Equipment value list where I choose the equipment used on this particular flight, that consists of Equipment::equipmentID (first field of value list) and Equipment::serialnumber (second field of value list), with only the 2nd field (serialnumber) being visible. This value list only shows the equipment that is related by the Location table (locationID).

Relating FLIGHT and Equipment via location allows me to select from the equipment that is actually on-site (i.e. if I am in CA, the value list will only show Equipment that is also in CA, versus listing all of the Equipment, to include those pieces at overseas locations).

The problem comes when the relationship is broken (e.g. in the future, due to Equipment movement, I will go into the Equipment table and change the fk_locationID to reflect the Equipment's new location).

After this occurs, pop-up menus show only the record number of the equipment, instead of the serial number that I have set up in the value list.

Researching this, I did find a 'solution' that entailed creating a second value list that didn't use Equipment::equipmentID (it only used one field, using Equipment::serialnumber), and using a FLIGHT field that I renamed to 'equipmentserialnumber' (since it now stores the serialnumber instead of equipmentID). To make the location realationship work, it was necessary to relate FLIGHT::serialnumber to Equipment::serialnumber, which creates a second Equipment occurrence (FlightEquipment, for example).

The problem I am having (and it is likely a rookie mistake, admittedly) is that, for other (more in-depth) areas of my database, I am having a helluva time relating other tables to the Equipment table through the Flight table (to gather only information about equipment used for a particular flight, for example). I'm nearly convinced that it is due to the serialnumber/location relationship workaround, as these items weren't an issue prior.

Do I have any other options to keep the Equipment::serialnumber visible in FLIGHT, after the relationship is no longer valid? I've spent two days wracking my brain on this, and being wet behind the ears on DB design, it's driving me insane (for reference, this project is the result of watching many DB design lectures, and attempting to convert an old flat-file Filemaker database to a 'real' relational database. The original intent was to use Postgres, however, I am not the only person that will be using this, and I don't have the knowledge necessary to ensure that it would be successful).

Thanks in advance for any insight! I apologize in advance if the explanation is unclear; sometimes it would be easier to simply post a file so others can see what exactly is going on, but with sensitive information included, that isn't always an option).

Link to comment
Share on other sites

On 6/14/2017 at 10:23 AM, vwgtiturbo said:

The problem comes when the relationship is broken (e.g. in the future, due to Equipment movement, I will go into the Equipment table and change the fk_locationID to reflect the Equipment's new location).

Well you definitely identified the problem.  Obviously your structure isn't correct or you wouldn't have all the relationship issues.  Can you post a file as a clone with some sample data?  Or can you at least post a picture of your relationship graph.   Describing more detail is always a plus.

Some questions:

-What is a flight?

-What is equipment and how does it relate to a flight?  If a flight is an actual plane, you should probably have a table for planes.  Then the flight is a join table.

-What happens to the equipment at the end of the flight?  Is it now in a different location and you need to change something, is it assigned to another flight, or does it return to it's starting location?

-Does the equipment have a home base and may need to be treated more like inventory?

-Is equipment bought and sold, or is it bought and used (or bought and rented/leased)?

-Is location related only to the starting and ending of the flight or does it also relate to the equipment independent of the flight.  IOW, using the flight as an actual airplane flight:

A plane flies from NY to England to France.  But the equipment only goes from NY to England, then gets put on a different flight that flies from England back to NY.  So are you tracking flight location and/or equipment location-either together or independently.

 

 

 

Link to comment
Share on other sites

Hi Steve, sorry for the late response; I thought I had answered this, but confused it with another question on a different forum.

I think I may have figured it out... for now. It all boiled down to my relationship. I was creating a table with each piece of equipment in the inventory, and one of the fields was its location, related to a location table in a one-to-one manner. This ended up being a bad move, as the equipment can move locations over time, so it likely should be a many-to-many (using an intermediary with one-to-many). In this manner, the junction table (EquipmentLocation) would have many records over time, with each piece of equipment's movement to another location filling a new record. I'm still wrapping my brain around it...

To answer your questions (not point by point, but operationally...). Each Flight is a mission that uses a particular aircraft, and a particular piece of equipment. The mission flies over a particular area, uses the equipment to gather data, then returns to home location.

The complexity comes in... each mission has a home base ('location') and each piece of equipment has a home base ('location') and these are related, such that when a mission is departing from location X, only the pieces of equipment at location X are considered for the mission. What I ran into earlier was: if I send a piece of equipment to another site (i.e. it no longer belongs to me) and then update that equipment's location in the DB, all of the previously created missions from my location that used that particular piece of equipment are now no longer related (such that Filemaker resorts to changing the equipment's 'serial_number' on my layouts to the record number of said serial number).

The solution (at least, in my VERY novice mind) was to move each equipment's location to a third table, such that each equipment location will be stored forever (every location that the equipment has been) which should keep the equipment related to any particular location forever (and Filemaker will always be able to show the serial number instead of the record number).

I think everything was the result of a simple relationship error; my thinking was that each equipment can only have one location at a time, therefore, one to one; reality says that equipment can have many locations over time (and locations can have many pieces of equipment at any given time) so a many-to-many (and junction table) is likely more proper.

The scary thing is that, early in the project, my Flight/Mission table was pulling from a separate value list to allow storing the equipment serial number as text (instead of the record ID) in order to avoid the 'location change/unrelated' issue, and I had an odd second occurrence/relationship set up to 'translate' the text serial to record ID for a relationship further down the line. And for a bit, it worked. Unfortunately, as I dug deeper into the project, other areas that relied on that relationship wouldn't work, so now I'm back to trying to do it the right way (which is a challenge for me, considering it is my first 'proper' database).

In any case, for now, I think the relationship was the issue, but if you think that I'm going in the wrong direction, please feel free to correct me! As stated, I am rather new, and looking to learn ;-)

Link to comment
Share on other sites

4 minutes ago, vwgtiturbo said:

Each Flight is a mission that uses a particular aircraft, and a particular piece of equipment.

Assuming each flight uses only one piece of equipment, the relationship between Flights and Equipment should be based on matching EquipmentID and nothing else. That way the relationship will not break when the equipment is moved to another location. If you want to select the equipment based on matching location, use another relationship for that purpose.

 

10 minutes ago, vwgtiturbo said:

the equipment can move locations over time, so it likely should be a many-to-many

That depends on whether you need to have a history of equipment movements. Note that if each flight has a location, then you already have a partial history, at least for the equipment used on flights.

Link to comment
Share on other sites

9 hours ago, comment said:

Assuming each flight uses only one piece of equipment, the relationship between Flights and Equipment should be based on matching EquipmentID and nothing else. That way the relationship will not break when the equipment is moved to another location. If you want to select the equipment based on matching location, use another relationship for that purpose.

Wow... Funny how reading that made things a bit more clear. I swear, I must've suffered from tunnel vision when going through this.

I'll be modifying the design a bit and see how it changes the behavior. I really hope that my more complicated issues are taken care by this as well (or at least, made a little more manageable).

Thanks for the wisdom!

Link to comment
Share on other sites

This topic is 2477 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.