search

phpvms / phpvms_v2

Virtual Airline Management (not maintained)
http://www.phpvms.net
BSD 3-Clause "New" or "Revised" License
41 stars 46 forks source link

Foreign Constraints #109

Closed Oxymoron290 closed 11 years ago

Oxymoron290 commented 11 years ago

Foreign Constraints. We need more of those. I'm submitting this issue because I was using this method in a new module I was developing and it works really well. Here is a list of some foreign constraints that would be useful to phpVMS:

phpvms_pireps.pilotid -> phpvms_pilots.pilotid phpvms_news.postedby -> phpvms_pilots.(too lazy to look at what feild it should be) phpvms_pirepvalues.pirepid -> phpvms_pireps.pirepid

The list can actually get pretty long but here are a few. I'll work on composing a list, but my time is limited, if someone else wants to pick up on this feel free to do so. I do feel as though this should be added as a milestone to v3.x. This will make the structure of phpVMS a little more professional and it will prevent tons of potential bugs possibly created by addons or code smell.

For any new developers coming along that wanna take on this task, or those of you who don't fully understand foreign keys watch the following video to get a grasp on the concept: http://www.youtube.com/watch?v=nm8y9gSi7O8

nabeelio commented 11 years ago

FKs were used quite heavily in the 'earlier' days, but ended up being more trouble than they were worth, and were subsequently removed. I'm also not a huge fan of FKs - with larger databases they tend to slow things down (not that any phpVMS databases are large enough to be so much of an issue) - but they also make cleaning/removing/updating records a bit tedious.

I'm of the opinion that those contraints should be handled by the application, unless it's a type of application where handling those restraints is not really an option.

Oxymoron290 commented 11 years ago

It's disappointing to me that you feel that way about Foreign keys. What were the specific trouble you were coming across when doing foreign keys? They don't really tend to become an issue when you just cascade down the changes/deletions. Possibly we can work on this and find a better solution for phpVMS? Or are you completely against it for this project?

Oxymoron290 commented 11 years ago

Side note: Having the application handle those constraints can make the application harder to maintain, causing those code smells I mentioned earlier.

nabeelio commented 11 years ago

In my long experience of developing on *sql and huge applications with multiple moving parts, FK's end up being a crutch that is relied on, where careful attention to where it should be paid would go a lot further. I'm not against them completely - in a very regulated, coordinated project, FKs do have their use, but in an OS project like phpVMS, not so much. They sound like they'd be convenient, but in a practical sense, there are many nuances.

Cascading down deletions is very dangerous - esp in the case where the data may be related in the data model, but that tight coupling will cause you to lose important data. For instance - when a PIREP is deleted, it would cause several other records to also be deleted; any application which then relies on that FK relationship, will not update any of the other records which may need to updated. It's also dangerous in an OS project, such as phpVMS, where the addon developers may not understand the intricacies of the FK'd relationships, and will just delete things.

Cascading on updates, in the way that I've organized the FK relationships is quite unnecessary - when a DB is properly normalized (which I believe phpVMS is), then the cacading of updates doesn't buy you anything.

In the case of FKs in phpVMS - between airlines, schedules, pilots - if you accidentally deleted an airline, or schedule - now all of your pilots and schedules would be lost - and you'd be SOL. This has happened multiple times in the past, where someone would delete something from the database, thinking it would just be that record - and then they lose a ton of data; no matter how much you make it clear, it will still happen. The first versions were VERY tightly FK'd with cacading deletes and updates. They proved to be a problem for updates and maintenance tasks, which is why they were removed over several versions. There hasn't been much code smell - if a project is architecture and planned, then it's not an issue. I've worked on quite a number of huge, commercial projects; FKs were very rarely used, and only in specific contexts.

Oxymoron290 commented 11 years ago

Well said. I see your point. I'll review the old versions of phpVMS and re-open if any questions arise. Thank you for your time.