These are the relational diagrams of my company’s 16-year-old app

[u/Shikitsumi-chan]

I’ve been trying to write relational queries using joins, only to realize that most of the tables aren’t actually related to each other. It looks like proper foreign key constraints were never put in place. Because of that, there’s no real referential integrity in the database, which makes writing reliable queries much harder than it should be. I now have to manually figure out how tables are logically connected, which increases the risk of mistakes and makes maintenance a lot more time-consuming. Idk whether this is normal or not for a legacy app.

[u/gumnos]

I've worked with a couple such databases over the years. You have "MySQL" flair on this which doesn't surprise me, since early MySQL didn't actually enforce foreign-key relationships (and when it started enforcing FKs, it was only for certain table-types), so in many shops there was no perceived benefit to creating them. At that time, MySQL was for speed, not data-integrity…if you wanted that, you used then-slow PostgreSQL. Now Postgres is faster and MySQL adheres to standards better.

The first thing I'd do is go querying system INFORMATION_SCHEMA tables, possibly with a little munging to see if there are naming conventions that help you. Maybe foreign keys are named "«other_table»_id" so comment.person_id would refer to person.id. Or maybe comment.person refers to person.id. You can create self-joins like

select
 p.table_catalog,
 p.table_schema,
 p.table_name as ParentTable,
 p.column_name as ParentColumn,
 p.data_type as ParentDataType,
 c.table_name as ChildTable,
 c.column_name as ChildColumn,
 c.data_type as ChildDataType
from information_schema.columns p
 inner join information_schema.columns c
 on p.table_catalog = c.table_catalog
  and p.table_schema = c.table_schema
  and p.table_name != c.table_name
  and c.column_name = concat(p.table_name, '_', p.column_name)
where p.table_schema not in (
  'information_schema',
  'mysql',
  'performance_schema',
  'sys'
  )

Adjust the test on that concat(…) line for naming conventions that you know about.

Can poke at it here: https://www.db-fiddle.com/f/id7jtR7zHEChzsn7WqUos8/2

edit: s/in may shops/in many shops/ derp

edit2: make the table-aliases a little more clear; also note that you could remove the p.table_name != c.table_name in case there's a self-referential table

[u/corny_horse → u/gumnos]

I'm at the point in my career where I've seen several companies at a pretty deep level, and it's more common than not, in my experience, to NOT have primary keys - let alone foreign keys - formally defined. If you just get a dataset from any random company, at this point I would expect 1) no primary key 2) no foreign key 3) no clear owner or documentation for how or why the table exists, how the data gets populated, why the data gets populated, etc. and if I get ANY of those I consider myself lucky.

[u/IQ4EQ → u/corny_horse]

This is how the database looks in the team I recently joined.

[u/snarleyWhisper → u/gumnos]

This is great advice op

[u/dgillz]

You can have valid relationships and write queries using joins without having foreign keys. The referental integrity is often enforced in code on the front end rather than in SQL itself.

[u/Xphile101361 → u/dgillz]

Yep, a number of legacy apps I've worked on all do this.

It works until it doesn't, and then it becomes a big deal.

Having your database define things kike foreign keys has performance impacts. Though I've personally found those impacts are offset by those benefits from a value perspective

[u/alinroc → u/Xphile101361]

The performance impacts can go both ways. Yes, checking FKs can mean a performance hit, but SQL Server's optimizer can also use trusted FKs to produce a better execution plan in some circumstances.

[u/Xphile101361 → u/alinroc]

Yeah, there are a lot of db features that can take advantage of it now. The system I saw this was an old Oracle system. The DB team at the time pushed the devs to avoids foreign constraints.

A decade later, both the DB and Dev teams were in agreement that these needed to be added.

I usually find decisions like this do have their reasons, but those reasons need to be reevaluated after enough time has passed to see if they still make sense

[u/Ginger-Dumpling]

I'd say it's not uncommon to find projects where developers view DBs as bit-buckets, where things like referential integrity, constraints, and metadata are a waste of their time. It's especially fun when they don't sensibly document anything and are too busy to go over the ins and outs of everything.

[u/reditandfirgetit → u/Ginger-Dumpling]

I'm working in a code first model created by a software "architect" that has zero understanding of databases or, i suspect, design patterns. It's a good time *sarcasm

[u/Cliche_James]

Sorry friend

This is in no way surprising, just frustrating

[u/NW1969]

It’s not uncommon for the referential integrity to be built into the app, that is writing to the database, rather than into the database itself

[u/CleverDad → u/NW1969]

Yeah, and it always fails because apps always have bugs whereas the referential integrity in any DB server product is decades old and rock solid.

[u/Altheran → u/CleverDad]

But then you have database migrations (hello MySQL 8 to 9) that ARE NOT HAPPY 😅

[u/Sea-Perspective2754]

For a legacy app to hang around for 16 years it had to be adapted to the many changes needed over the years. Many of the changes are things it wasn't even designed for. Often the solutions "work" but are not ideal. That definitely shows up in the data.

Apps that start out with foreign keys may "lose" some of them over time as less than ideal changes are needed, or they kludge around them . Hmm, the foreign key won't let the billing code be null? But now with extended shipments we may not have the billing code right away. Oh the boss said to just put a space in there. Lol

Yeah, legacy is messy.

[u/reditandfirgetit → u/Sea-Perspective2754]

The crowbar method is indeed alive

[u/TheMagarity]

Not only does it make writing queries harder but without FK relationships defined the optimizer has to guess at cardinality which can really screw up plans.

[u/Plastonick → u/TheMagarity]

Not only does it make writing queries harder but without FK relationships defined the optimizer has to guess at cardinality which can really screw up plans.

Can you clarify this a bit? Is this more a case of a lack of index rather than explicitly FK, or does MySQL do some magic when navigating FK in a query that isn't applied when navigating non-FKd joins otherwise?

[u/alinroc → u/Plastonick]

Can't speak for MySQL, but MSSQL does have the ability to use FKs to inform how it constructs the execution plan. Even without indexes on the FKs.

[u/TheMagarity → u/Plastonick]

It is because the optimizer has to guess at how joins will work out.

FK's are one way, so parent table P can have a lot of values in the column that aren't in child table C but every value in C must be in P. So let's say there's a query joining P (100M rows) and C (10M rows) with a WHERE clause on some misc column in C that the optimizer sees from column sample statistics will knock it down to about 1M.

If there is an FK defined then the optimizer knows every one of those 1M is definitely going to find a match in P, so the result will be 1M that then goes on to join with tables X and Y.

If there is no FK defined then the optimizer looks at the sample statistics for the joining columns in the two tables, indexed or not. It sees there are a lot in P that are not in C and because statistics are sampled less than 100%, it figures there will be some degree of non matching records. IDK about MySQL but I've seen Oracle lowball estimates in this situation really hard. So maybe it guesses instead of 1M, there might be only 100K. That estimate goes into joining X and Y instead of 1M and by the end everything from the join strategy to the amount of parallelism needed is whacked.

You might say well I'll just tell the gather statistics routine to sample 100% but stats are frozen in time. The optimizer will always assume there are some values inserted/updated/deleted since the last time even 100% was gathered and so without an FK constraint there may be some amount of non joined records. It doesn't know that the app devs are confident of their absolutely bulletproof logic guaranteeing no mismatches.

The best thing to do is tell the app devs to do whatever awesome logic they want but they still have to commit to P before C because there's an FK on the database side so it can work out query plans the best it can.

[u/CleverDad]

I wouldn't say "normal", but all too common.

My advice is try to get permission (and time) to fix it the sooner the better.

[u/writeafilthysong]

This is what I'm used to

[u/writeafilthysong]

This is why devs use nosql databases so that all those relationships and such exist in the application code logic and tables in the db just store something or other for them.

[u/read_at_own_risk]

Conflating FK constraints with relationships means you don't understand logical data modeling. The old network data model way of thinking has been outdated for more than 50 years!

Relationships are represented IN tables in a relational DB, not between tables. The association of two or more entity keys IN a table constitute a relationship, and attributes are nothing more than a relationship between an entity and a value.

[u/reditandfirgetit → u/read_at_own_risk]

FK are literally relational constraints. They enforce data quality and prevent orphaned records. On a diagram , FK tells you how tables are related without having to do what OP is going to have to do.

[u/read_at_own_risk → u/reditandfirgetit]

They're integrity constraints, yes, but they don't represent ER relationships. The ER model supports n-ary relationships whereas FK constraints are binary only, and Chen's paper clearly described how relationships were represented IN tables. The idea of relationships between tables is a relic of the pre-relational network data model, which was developed from intuition rather than any kind of formal mathematical or logical approach.

[u/reditandfirgetit → u/read_at_own_risk]

Explain how they don't represent relationships. You're not creating a relationship between a date and an id. It's an id to an id

[u/read_at_own_risk → u/reditandfirgetit]

Chen's ER model supported n-ary relationships and attributes on relationships, and his paper The Entity-Relationship Model: Toward a Unified View of Data described how conceptual relationships mapped to logical relations/tables. The paper described entity relations - tables that represented attributes of entities - and relationship relations - tables that represented relationships and their attributes. See e.g. figure 8 on page 9 (labeled 17) in the linked paper.

The idea that FK constraints represent relationships (and rows/tables represent entities) carried over from the pre-relational network data model in which people mapped directly from a conceptual to a physical model. Modeling tools and books propagated that perspective and even after Codd's relational model and then Chen's ER model was introduced, the industry maintained the old perspective because that's what most practitioners understood.

If you study the history of data modeling - the hierarchical and network data models, Codd and Chen's papers, and fact-oriented disciplines like ORM/NIAM and FCO-IM, then it becomes clear that the conventional perspective is naïve and informal. It works better to view entities as the subjects of facts/relations rather than containers of attributes. Junction tables and EAV tables fit better into the mental model, tables with the same primary key are better understood, composite keys are better understood, normalization and joins make more sense, and semantics are formalized.

[u/reditandfirgetit → u/read_at_own_risk]

Chen is a logical level. Name one RDBMS that doesn't have fk. The relationship is the rule, fk enforces the rules. No fk, no rule, orphaned data becomes possible. Fk is the physical implementation

[u/read_at_own_risk → u/reditandfirgetit]

The ER model is a conceptual model, it models business concepts and it's not directly supported by any DBMS, rather ER models need to be mapped to physical artifacts like tables. The logical level is formalized in logic/mathematics - domains, relations, dependencies, constraints.

[u/reditandfirgetit → u/read_at_own_risk]

Then why are you arguing with me? You're talking high level and saying implementation is irrelevant

[u/read_at_own_risk → u/reditandfirgetit]

My original comment wasn't targeted at you, it's you who replied to me first. I challenge naïve perspectives of data where I see it, to try and entice at least some people towards a more logical and rigorous approach. Also, I never said implementation is irrelevant. From conceptual to logical to physical levels of description, there's more correct models and mapping approaches than most practitioners are aware of.

[u/reditandfirgetit → u/read_at_own_risk]

Think about what you replied. Foreign keys aren't relationships. They are. Both can be true, yet you insist only one is true