0

I am experimenting with database referential integrity and I have some doubts. I have a design that uses composite keys and it has referential integrity but I find that composite keys may be difficult to deal with at the app level.

The following tables are a simplification, I have tables with 4 and 5 columns as a composite key. There is full referential integrity, it's a fortress, and it makes a lot of academic sense, but as a pragmatic programmer I am thinking this is going to be a rabbit hole at the app level. So I was wondering how would the schema look like with surrogate keys while still maintaining referential integrity, if such a thing is possible.

The simplified schema using composite keys look like this:

CREATE TABLE organisations (
  id integer NOT NULL,
  name character varying NOT NULL
);
ALTER TABLE ONLY organisations ADD CONSTRAINT organisations_pkey PRIMARY KEY (id);

CREATE TABLE invoice_series (
  natural_key varying NOT NULL,
  organisation_id integer NOT NULL
);
ALTER TABLE ONLY invoice_series ADD CONSTRAINT invoice_series_pkey PRIMARY KEY (organisation_id, natural_key);

CREATE TABLE spaces (
  natural_key integer NOT NULL,
  organisation_id integer NOT NULL
  invoice_serie_id integer NOT NULL
);
ALTER TABLE ONLY spaces ADD CONSTRAINT spaces_pkey PRIMARY KEY (organisation_id, natural_key);
ALTER TABLE ONLY spaces ADD CONSTRAINT fk_spaces_org FOREIGN KEY (organisation_id, natural_key) REFERENCES invoice_series

The simplified schema without composite keys looks like this:

CREATE TABLE organisations (
  id integer NOT NULL,
  name character varying NOT NULL
);

CREATE TABLE invoice_series (
  id integer NOT NULL,
  organisation_id integer NOT NULL,
);

CREATE TABLE spaces (
  id integer NOT NULL,
  organisation_id integer NOT NULL
  invoice_serie_id integer NOT NULL
);

I could do this:

execute "ALTER TABLE spaces ADD CONSTRAINT fk_rails_invoice_serie_spaces FOREIGN KEY (organisation_id, invoice_serie_id) REFERENCES invoice_series;"

But that will force me to add an unique index on invoice_series with :organisation_id and :id. Which seems a really weird index to me.

What would be a better approach?

--

An organisation may have 0 or more Spaces A space belongs to one organisation

An organisation has 1 or many Invoice Series An invoice series belongs to one organisation

A space has one Invoice Serie An Invoice Serie may belong to 0 or more spaces.

Given these tables how do I ensure referential integrity, so that a space can only be associated with an invoice serie belonging to the same organisation?

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
Nerian
  • 15,901
  • 13
  • 66
  • 96
  • 1
    "force me to add an unique index on invoice_series with :organisation_id and :id. Which seems a really weird index to me." What is the problem? Your two designs are essentially the same, and in the first design you have a PK, ie UNIQUE NOT NULL, on those two invoice_series fields. – philipxy May 24 '17 at 23:59
  • It would be helpful if you said more about things that you are concerned about, as technically as possible. Because we don't know what you mean when you write things like "difficult to deal with at the app level", "makes a lot of academic sense", "rabbit hole at the app level", "force", "really weird" and the ubiquitous "better". Your final statement is clear, but since the way to do it is a unique index, and the designs are essentially the same, your actual question seems to be about what notions/principles you should abandon & which you should use instead. If you clarify, we can address. – philipxy May 29 '17 at 05:06
  • The difficulty I see at the application level is that it is a lot harder to use composite keys than just a single id. I am using Ruby on Rails, and the support for composite keys is lacking. So using composite keys increases the complexity of the code I have to write. Indeed both designs are equivalent, at the DB level, but at the Application level code is a lot simpler if I can just use a one column id. This design requires a composite index with :organisation_id and :id and by weird I meant that having the id on the index seemed redundant, but I get why is needed. – Nerian May 29 '17 at 19:59
  • But in any case I was wondering if this is how referential integrity is usually done for the kind of relationships I described, or if there is a better way. – Nerian May 29 '17 at 20:00
  • 1. Please edit your question; comments are not for clarifications. Please read about [ask], in particular giving versions of the sytems you are using. And see my earlier comments. 2. PS What does "having the the id on the index" mean? You keep writing strange short phrases. See my earlier comments. 3. What have you learned from googling various statements of your problem? [Eg.](https://stackoverflow.com/q/41888549/3404097) 4. In general, add an API-friendly PK column, but have necessary constraints. Adding a surrogate *increases* them, because now a PK & composite FK in a table must agree. – philipxy May 29 '17 at 23:32

0 Answers0