Multi-level confirmation process

To counter the points directly:

Drupal doesn't use them and gets along fine without them, so why should we?

Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively meaningless. I know people who don't wear a seatbelt in cars, but I do as do most others...

They're too inflexible when you need to change data and/or structure.

If you are changing your structure to the point where you need to change FK relationships then anything in your existing design can look inflexible (particularly if the existing design didn't properly match the system you were modelling to start with, which is probably why you are looking to alter the existing structure).

They are deliberately inflexible when changing data. They are designed to not let you change data in such a way that integrity could be compromised, even temporarily. Ask for examples where a foreign key constraint would stop a data modification and for each one we'll be able to explain why that is a good thing and how to work with the constraint to achieve your goal without breaking it (or conversely, we might instead tell you why that particular FK relationship is wrong - but that doesn't make other FKs wrong it just means there was a problem with that part of your model).

He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them.

Removing them did not cause corruption. Removing them allowed a bug in some other code to cause corruption over time and it wasn't noticed. If the keys had been in place that process would have raised an error and presumably the problem with it would have therefore been noticed and fixed instead of silently being allowed to break more data until noticed. All removing the constraint does is stop the database enforcing the constraint for future inserts/updates/deletes - it does not affect existing data.

I'm not sure how you'd go about conclusively proving to someone who is sure otherwise that this is the case.

The only proof I can think of is first principals: reasserting what foreign key constraints actually do thereby showing that they would not allow inconsistency to appear, and if there are specific examples of the corruption the person has in mind you could work through trying to create that problem with the keys in place (showing that the key would have caused an error instead of allowing the inconsistency to be created).

More specifically about the immediate effect of removing the constraints: show that dropping them does not alter existing data at all. Create a copy of the database, drop the keys in the copy, and run a full comparison of the data to show that nothing changed as a result of the constraints being lifted.

he's against (confident that he can enforce RI at the application level).

At best that is bad use of development/testing time, at worst it will create a nightmare for later. You are reinventing the wheel, probably inefficiently and potentially with bugs, you have to implement your new wheel everywhere code touches that data in every application now and going forward instead of simply letting the database handle it each time, and if one of those bits of code has a bug the data potentially loses integrity for all applications for all time.

There are of course complex business rules that a DBMS can't enforce for you, so you have to implement them in your BL layer, but for the fundamentals like this let the DB handle it.

An extra one not mentioned but I've heard several times: They are inefficient when we make changes to the parent objects

This is due to most DBMSs not automatically creating an index for each FK, many people assume that they do and are surprised that they don't see the performance metrics that they expect. DBs don't do this as it could be very wasteful when not needed (which is more often then you might expect). If you need an index on the columns of the FK, to make cascading updates/deletes (or just simple join queries in that direction) efficient, create one.

When doing this sort of design, I think first about the "things", and then about the "relationships". The things are:

• the people
• the credit cards and
• the organisation.

The relationships are:

• Who owns the organisation.
• Who is a member of the organisation.
• Which credit card is the owner using to pay the bill

You need an entity for each "thing". Depending on whether it is a "to one" relationship or a "to many" relationship, you can either define the relationship within an entity or you will need a new entity for the relatonship.

Who owns the organisation: If only one owner, you can put the owner in the organsation entity, otherwise you would need a separate entity. Who is a member of the organisation: You can have many members of an organisation, therefore you will need a separate entity. Which credit card is the owner using to pay the bill: Only one credit card would be used therefore you can put the reference to the credit card card in the organisation entity.

So you would have four entities: Person (a "Thing") Credit Card (a "Thing") Organisation (a "Thing", with two relationships) Organisation_Member (a relationship)

Here are some example columns you could use for the entities

Person Columns:

• person_id (primary key, identity)
• name
• email

Credit Card Columns

• person_id
• billing_id (start from 1, don't make it an identity)
• +credit card information columns (I expect you would encrypt the data, so not store the actual card numbers as text)

(The combination of person_id and billing_id in the above forms the primary key)

Organisation Columns:

• org_id
• org name
• person_id (of the (one) owner - defining one relationship mentioned)
• billing_id (of the (responsible) owner - defining another relationship mentioned)

Organisation_Member:

• org_id
• person_id (of the member)

One more point about email addresses. If you want there to be a separate email address for the organisation, it can be included in the organisation table. I'm not sure I understand why you would need this. Perhaps my answer would be different if I understood what it is for.