Have you ever worked with Database no "Relations", no "PKs", and no "FKs", just raw data?

Question

Hi all.

Nowadays, I'm working on a database, with no "Relations, PKs and FKs", just raw data. I can say that database is just set of papers. When I asked about this, I had this; "Hide the Business".

Also, one of my friends said, this always happens in "Large systems".

In large systems, they are tyring to hide thier business through raw data. Regarding development; relations, constraints, validation, are done in database using triggers and of course user interface.

What do you think regarding this?

Answer 1

I would say sacrificing data integrity for security through obscurity is a bad trade.

Answer 2

Primary Keys, relations etc etc are tools for making database development easier and for making the final result faster and more efficient. I can only think of a few rare cases where not having a key/index would be a good idea.

Did your friend explain why they held this view?

Answer 3

I think I came across at least two applications with databases lacking relations and FKs.

The idea is probably that it's more difficult to reverse engineer the brillant database schema.

The side effect is that often the applications are not so good at checking constraints themselves, leading to lot of rubbish data in the database, which in fact does make it more difficult to reverse engineer, as FK constraints are not enforced ;)

My view is that once it's a database, somebody else can look into it, and trying to work around this "feature" of visibility is pointless and generally Not a Good Thing, considering the drawbacks (no relations, no SPs, no triggers, etc).

Answer 4

Well, this may have point on large databases, when you need fast responce on massive DML (INSERT / UPDATE / DELETE).

The problem is that if you rely on database's way to ensure integrity, you hardly can optimize it.

There is also thing called SQL/PLSQL context switching in Oracle: if you create an empty trigger on the table, it will slow down DML about 20 times — with the mere fact that the trigger exists.

In Oracle, when you write a ON UPDATE trigger and update 50,000 rows in the table, the trigger and the query in it gets called 50,000 times. Foreign keys perform better, but they may also get laggy (and you can do nothing with the underlying queries)

In this case, it's better to put the results you want to update into a temporary table, issue a MERGE, check integrity before and after, and apply the business rules. A single query that processes 50,000 rows works faster than a loop of 50,000 queries processing single row.

Of course, it's very hard to implement and only pays for itself when you have really large database and need to perform really massive updates on it.

In Oracle, in any case, FOREING KEY constraints perform better than tiggers implementing the same functionality.

PRIMARY KEYS will most likely improve performance, as a primary key implies creating the UNIQUE INDEX on the constrained field, and this index may be efficiently used in the queries. A UNIQUE INDEX is also a natural and most efficent way to enforce uniqueness.

But of course, as any index, is slows down INSERTS and those UPDATES and DELETES whose WHERE condition is not selective.

I. e. if you need to UPDATE or DELETE 1 row of 2,000,000, then the index is your friend; if you need to UPDATE or DELETE 1,500,000 rows of 2,000,000, the index is your enemy. It's a matter of tradeoff.

You may also see my answer here.

Answer 5

This kind of thing can happen in financial systems. It's the opposite of what you'd expect, you'd think that because it's finance that best practice would be applied more rigorously. However, the converse is often true. Many of these databases may have started out in excel.

I have seen a number of databases where they don't bother with fks or pks. I can't say I like it, but sometimes you have to have to just live with it, or leave and go work somewhere else for less money but with more database integrity.

Perhaps this is why they need you.

Answer 6

(me=MSSQL)

Deleting a row form a large table that has lots of FKs is slow.

Our APP has never been denied a Delete for a FK constraint violation on that table

I have considered dropping the FKs to improve performance.

Too chicken to have actually done it though :(

PS We would keep the FKs on the DEV / TEST systems

Answer 7

Maybe this was converted from a former system that used a file based structure to hold the data. The tables in the new database are just a reflection of the individual files.