1

I want to add a field entity_id to finance_invoice. It will speed up (minimally) reads and simplifies the query.

select * from finance_invoice where issuer_identity_id in (
    select id from finance_identity where entity_id = @container_entity_id
)

vs

select * from finance_invoice where container_entity_id = @container_entity_id

container_entity_id could be derived from issuer_identity_id, since an identity belongs to an entity.

Schema: 

table - finance_invoice
field                 | type
id                    | int(10) unsigned
issuer_identity_id    | int(10) unsigned
recipient_identity_id | int(10) unsigned
reference_id          | int(10) unsigned
...

table - finance_identity
field                 | type
id                    | int(10) unsigned
entity_id             | int(10) unsigned
reference_id          | int(10) unsigned
... name, address fields ...

table - finance_entity
field                 | type
id                    | int(10) unsigned
type                  | int(10) unsigned
...

foreign key - invoice_issuer on finance_invoice
finance_invoice.issuer_identity_id references finance_identity.id

foreign key - invoice_recipient on finance_invoice
finance_invoice.recipient_identity_id references finance_identity.id

foreign key - identity_entity on finance_identity
finance_identity.entity_id references finance_entity.id
Olavi Sau
  • 1,647
  • 13
  • 20
  • 1
    If invalid data can be recorded, it eventually will be. I would advise against capturing the redundant data. Maintaining it when a new identity is created for an entity is going to add complexity to your system. If you must include it, look into a composite foreign key constraint at least to enforce validity. – reaanb Jul 20 '17 at 06:27
  • I'll look this question over completely once and if I have the time for it. I agree it's fairly bad and awkward question, but I couldn't find another like it - probably because of my lack of knowing the terms. Title changed. – Olavi Sau Jul 24 '17 at 17:10
  • @philipxy It's also obvious that trying to use remove the ids from field names to reduce clutter is a garbage approach, i will end up confusing people.Thanks for the feedback. – Olavi Sau Jul 24 '17 at 17:13
  • The FKness - no idea how to express that, with the new design redundancy is minimal, other tables use (container_entity_id, ...) as FK. I added the subselect, that is the most common read on finance_invoice. About the "container" - i am still deciding on the name. – Olavi Sau Jul 24 '17 at 18:57
  • Re FKness: A column can be derivable/redundant in many ways, some function of other rows/tables. By the "FKness" of entity_id I was sloppily referring to its specific derivation/redundancy being that one could have followed a FK in the same row. Good edits. I didn't find much in-depth from googling some variations of your question (adding a fk derivable from other data) but surely there are some informative answers. It's a basic example of the usual tradeoff--more update work for fewer joins. Don't forget to consider a view. – philipxy Jul 24 '17 at 22:55

1 Answers1

0

I figured it out.

In order to enforce referential integrity I needed to add the container(creator) entity id to both invoice and identity.

table - finance_invoice
field                 | type
id                    | int(10) unsigned
container_entity_id   | int(10) unsigned
reference             | varchar(255)
issuer_identity_id    | int(10) unsigned
recipient_identity_id | int(10) unsigned
invoice_date          | date
due_date              | date
currency              | char(3)
created_at            | datetime
cancelled_at          | datetime
cancelled_by          | int(10) unsigned

table - finance_identity
field                 | type
id                    | int(10) unsigned
container_entity_id   | int(10) unsigned
reference             | varchar(255)
entity_id             | int(10) unsigned
... name, address fields ...


foreign key - invoice_issuer on finance_invoice
(issuer_identity_id, container_entity_id) references (id, container_entity_id)

This also solves the issue of fetching the records from both tables, I can use the entity id to fetch both identities or invoices, I also eliminated the need to join with references and the finance_reference table will only be a validation table from now on.

Olavi Sau
  • 1,647
  • 13
  • 20
  • I can't make much sense of your question but if this is the answer then it seems that [this](https://stackoverflow.com/a/44769965/3404097) and [this](https://stackoverflow.com/a/45251799/3404097) might help you. – philipxy Jul 24 '17 at 13:50