1 Topic by papafrankc (edited by papafrankc 2021-08-05 04:32:10)

  • Registered: 2020-06-26
  • Posts: 302

Topic: Join Questions

Hi All,
I'm having some problems with setting joins between tables. I believe it should be pretty basic and straight forward.  But I'm getting myself confused (easy to do sometimes lol).
Here's what I would like to do. Please refer to the attached PDF file.
.
- Vessel should be the PARENT table
- Owner should be the CHILD table
- If I DELETE a record from the PARENT table, I would like to also delete the corresponding record from the CHILD table (Owner)
- Simple right?
- So logic tells me to join the Owner table (child) to the Vessel table (parent)
- However when I do that, it screws up all kinds of things in my application.
.
The way I have it in my attached pdf file seems to work the best for my application, however when I delete a VESSEL record it DOES NOT delete the OWNER record.
- I understand why my solution is not working but I can't seem to come up with how to make it work.  I think, as I said that I'm confusing myself.
.
Right now I can delete a record from one of the other tables - MANUFACTURER, SUPPLIER...etc.  However I am NOT able to delete an OWNER record without first deleting the VESSEL record.  I don't understand this one because I don't believe I have the Cascade option set in any of the joins??
.
Note: in this application I will have - one Vessel and one Owner.  The other tables, Manufacturers, Suppliers, Equipment...etc. will have many entries.  I would like to leave the data in these tables even if the user deletes a Vessel and Owner and replaces them with a new Vessel and Owner.
.
Some thoughts about how PARENT/CHILD joins should be properly set up would be appreciated.
.
Thanks
Frank

Post's attachments

Attachment icon Join Issues.pdf 164.61 kb, 182 downloads since 2021-08-05 

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Join Questions

papafrankc wrote:

Hi All,
I'm having some problems with setting joins between tables. I believe it should be pretty basic and straight forward.  But I'm getting myself confused (easy to do sometimes lol).
Here's what I would like to do. Please refer to the attached PDF file.
.
- Vessel should be the PARENT table
- Owner should be the CHILD table
- If I DELETE a record from the PARENT table, I would like to also delete the corresponding record from the CHILD table (Owner)
- Simple right?
- So logic tells me to join the Owner table (child) to the Vessel table (parent)
- However when I do that, it screws up all kinds of things in my application.
.
The way I have it in my attached pdf file seems to work the best for my application, however when I delete a VESSEL record it DOES NOT delete the OWNER record.
- I understand why my solution is not working but I can't seem to come up with how to make it work.  I think, as I said that I'm confusing myself.
.
Right now I can delete a record from one of the other tables - MANUFACTURER, SUPPLIER...etc.  However I am NOT able to delete an OWNER record without first deleting the VESSEL record.  I don't understand this one because I don't believe I have the Cascade option set in any of the joins??
.
Note: in this application I will have - one Vessel and one Owner.  The other tables, Manufacturers, Suppliers, Equipment...etc. will have many entries.  I would like to leave the data in these tables even if the user deletes a Vessel and Owner and replaces them with a new Vessel and Owner.
.
Some thoughts about how PARENT/CHILD joins should be properly set up would be appreciated.
.
Thanks
Frank

Did you try checking the Cascade option?

brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Join Questions

Brian,
Yes I have tried the Cascade option in both tables - OWNER and VESSEL.
It doesn't seem to have any effect.
.
I have another application where the Cascade option works fine.  The extra tables in this application have a relationship that points back to my primary table.  When I delete a record in the primary table it deletes all of the records in the joined tables.
.
But not in this application.  My gut feeling is that it's something basic, but I'm not seeing it.
Thanks for looking into this for me
Frank

4 Reply by brian.zaballa (edited by brian.zaballa 2021-08-05 07:21:22)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Join Questions

I'm not so sure on the problem too. Maybe you can upload a sample application with the tables associated with the problem and upload it here for us to see the problem. Cascade option should be fine in this case.

brian
  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Join Questions

Oh, I think I got what you are trying to do. Why would you want to delete Owner's record after deleting Vessel?
Based from your database structure, Owner can get one or more Vessel. So if you put a cascade on Vessel.id_Owner, then deleting a record from Owner will also delete all Vessels under the owner. That's how it works with the structure. That's how child-parent works.

I'm just confused on why, but if you really want Vessel to be the parent, then Owner must be the one connecting to the Vessel and must have the id_Vessel. But it's like saying that a Vessel can be owned by one or more Owner.

If you really want that approach, here I made a sample to give you an idea.

Post's attachments

Attachment icon SampleFrank.zip 550.97 kb, 174 downloads since 2021-08-05 

brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Join Questions

Brian,
Many thanks for your reply.  Your example does exactly what I was looking to do.
Since this is a new application I'm open to changing my mind if things should be done differently. However at this time, here's why I've been thinking of doing it this way.
.
- I'm thinking of having only ONE Vessel and ONE Owner.  There will also be tables for Equipment, Service, Manufacturers and Suppliers  There are a number of scenarios that I think could affect the design of the program:
1 - the Owner sells the vessel and replaces it with another one.  In this case I would want to delete the Vessel, Equipment and the Services performed for that Vessel.  The other tables should remain for the new vessel.
2 - most Owners have only 1 Vessel at a time so I don't see the need to track multiple vessels.
.
Thinking more about it, the Owner table should probably be the Parent table and the Vessel table should be the Child table along with Equipment and Services.
.
I notice in your sample program that the Vessel is the parent and that you put the id. link in the parent table, pointing to the child table.  When I've been setting up relationships I've been thinking that the  id. link should be in the child table, thus pointing back to the parent table.  I've thought this because when setting up a relationship it says Select the parent table to link.  I believe this has been one of my major confusion factors. Please let me know if I'm not understanding this correctly.
.
I noticed in your Script file that you have a number of update commands.  I think my not having any is what may be giving me some trouble.  I said earlier that I could delete a record in the parent table and nothing happened in the child table.  I think that by updating it might fix my issue of nothing happening to the child table.
.
Ultimately, if an Owner sells a vessel I will need to remove that Vessel and all the Service and Equipment records.  Leaving Manufacturers and Suppliers alone so they can be used for the new Vessel.
.
You've given me a lot to think on.  I now need to go back & see what other kind of trouble I can get into.
.
Thanks for all you do to support and help us wanna-be programmers.
Frank

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Join Questions

Good day Frank,

From your statement, 2 - most Owners have only 1 Vessel, Will there be Owner/s that eventually have more than one vessel at a time?

The update queries I made there is just to fill up the gaps on the logic. as you can see, used(is_used=1) owners will not appear on the vessel form when you create one. This will prevent having an Owner with multi-Vessel (for this will cause of the foreign key error when auto-deleting the Owner). I just go with the business logic you are trying to do. But again, in my opinion, there's no need to automatically delete owner after deleting a vessel.

For the child-parent, from my sample(copied from your pdf), Parent table is the Owner, and it has Child Vessel following the One-to-many relationship.
One Owner can have one or more Vessel/s.

Again in my opinion, to save you from that trouble of having that additional queries, scripts and checking if the owner is already in a vessel, don't auto-delete Owners that will no longer have vessel or after deleting a vessel. Easiest way to do that is to have another module on your application that will check all owners that have no vessel, then delete them,

brian
  • Registered: 2020-06-26
  • Posts: 302

Re: Join Questions

Brian,
NO - I would like for One Owner to only have 1 Vessel at a time.
.
- I was testing the program that you sent me and it looks like when I delete a Vessel/Owner record from Form 1, it automatically deletes both the Vessel and the Owner from form 1 AND it also deletes the Owner from form 2.
- Then when I delete just an Owner from form 2 it also deletes the Vessel/Owner record from form 1.
.
So it doesn't look to me like the cascade option seems to be doing anything?  Maybe I'm missing something.
.
I also noticed from your last update that you are saying that Owner is the parent table.  If so is it then correct that the link ID should go in the child table (Vessel)?  This concept is where I've seen conflicting scenarios and getting confused.
-
As I mentioned in one of my earlier posts I've always thought that the child table should contain the link ID and point back to the parent table.   If you could please confirm this then I think it would help me when setting up a new program.
.
Thanks for your patience and help.
Frank

9 Reply by brian.zaballa (edited by brian.zaballa 2021-08-07 05:35:18)

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: Join Questions

The cascade is working just fine.

In that database structure, Owner is the Parent, and it has Child Vessel. The table having the id_[ParentTable] field is the Child. That's how One-to-Many table works.

Setting id_[ParentTable] to cascade will allow or automatically delete the record connected to the Parent. That's why when you delete Owner, it will automatically delete its child Vessel (only child since you only have one vessel for each Owner). That's how the cascade works.

In that case, in my sample. Owner is the Parent, it is how database work, but since in your business logic, I just make a little tweak when deleting the Vessel (script in Vessel Delete button). Tweak to automatically delete Owner when it has been deleted since there's no such function in SQL to automatically delete Parent's record when deleting record from its Children (not really tho. you can also create a trigger for auto-delete but that'll make it more complicated unless you are familiar with it).

Problem with this approach is when you connect other Child table of the Owner, say for example Owner having Transaction table, or Sales or whatsoever that is connected to the said table(Owner), automatic deleting this(Owner) will throw you an error if you didn't select cascade for the other child. That's another topic by the way. You must know all the possible connection of the Owner before applying this approach.

If you are not doing anything with Owner table(this is my assumption since you are automatically deleting it after deleting a Vessel), then why not just include it in Vessel's table as fields? I mean all its fields(or just the important ones).

brian

10 Reply by derek (edited by derek 2021-08-07 17:45:28)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Join Questions

Hi Frank, Brian,
Just been reading through the thread of this topic and I think the most significant piece of information you provide is
"..... I would like for 1 owner to only have 1 vessel AT A TIME".
This tells you 2 things;
1.  there is NOT a one to many relationship between 'owners' and 'vessels'.  Therefore, there is no need for an 'owners' parent table with a 'vessels' child table holding multiple vessel records against that 'owner' (or vice versa).
2.  that the owner, although only having one vessel AT A TIME, MAY own a different vessel at some future point.  As there is a possible requirement to use the owner again, the owner details should be held in a 'look-up' table so that it can be referenced again.
nb.  if the owner was only ever going to have one vessel and NEVER acquire a different one, then, as Brian has mentioned, you would simply hold the owner details directly on the 'vessel' table.
So, in a nutshell, I would have 'vessels' as the main table and 'owners' as a look-up table. 
And looking at the .pdf in your original post,  it looks like all of the other tables in your schema (country, states, style, type etc) are all similarly  'look-up' tables that your main table ('vessels') references.
This should also clarify your issue about deleting data, with only the relevant 'vessel' record being deleted and the 'owner' record being retained for future use.
Finally, you are correct - in the case of a one to many relationship, the parent-id is always held on the 'many' side.
So, in your example, there is no 'child' table as such (as far as I can see from the .pdf) and id's are held against the 'vessel' record to reference the appropriate records in the 'owners,  'states', 'styles' look-up tables etc), 
I'm wondering whether part of your dilemma lies in assuming that there has to always be a 'parent' and a 'child' and you've been trying to squeeze your schema into that paradigm?
Anyway, I hope the above has done more to clarify than confuse - LOL!
If you've still got concerns, upload your project and we can have a further look.
Regards,
Derek.

  • Registered: 2020-06-26
  • Posts: 302

Re: Join Questions

Derek, Brian,
I really appreciate the effort you guys have put into helping me with my new project.
Maybe a little further explanation might help explain what I'm trying to do.
Goal of the program:
- for 1 Owner to have 1 Vessel (boat)
- be able to save info about the Owner such as name, address, city, state...etc.  I currently have 24 fields in the Owner table
- be able to save info about the Vessel such as make, model, length, registration nbr...etc  I currently have 29 fields in the Vessel table.
- the other tables - Equipment, Manufacturer, Supplier, Construction, EquipType...etc. all refer to details about the Vessel or the Equipment on the Vessel.
- the meat and potatoes of the application will be the Service table.  I haven't really gotten into this one yet.  I want to keep stuff like Date purchased, Date last serviced, Next service Date...etc.
- the main purpose of the program will be to allow the user to be able to see what service is coming up and when.  And also keep a record of services performed.  Keeping info about Manufacturers and Suppliers is also important when you need to buy stuff.
-----------------
For background info, my wife & I lived on boats for over 20 years.  It was a really neat experience.  I used to keep all my boat records in file folders.  And I used to keep most of the maintenance schedule stuff in my head.  Needless to say that's not very efficient.  And if you don't perform the necessary maintenance the results can be pretty drastic.  So I want my user to be able to go to the program and see at a click the upcoming maintenance chores that need to be done.  I might eventually throw in trip information, but that's a ways off. 
--------------------
I have taken your thoughts and recommendations and pretty much straightened out the Vessel/Owner problem.  The Vessel is the parent and the rest are basically "look up" tables.
.
Derek, I assume when you say "lookup" table you mean one that is NOT set to cascade.
.
When I get to the Service stuff I believe it should be tied to Vessel and cascaded.  If the Owner sells his boat and replaces it with another, then the prior service records should be deleted with the vessel.
.
Sorry for the long winded reply, but what can I say, I'm Irish and I tell long stories.
.
Thank you both again.  You have helped me unscramble some of my issues.  And I'm sure you'll be hearing from me soon as I get into Servicing records.
Frank