Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Design better databases

web.archive.org

Post reply on HN
Print view View original

171–180 of 182 posts

Re: Design better databases

#171

Post by vkjv »

iamleppert wrote: post #168

Looking around I see lots of people just blindly using UUIDs for everything. At a prior job, I had a boss who forced me to use UUIDs for everything, before we had even collected a single row of data. It hurts my pragmatism to solve a distributed systems problem before we even had a distributed system! Don't be a sheep and use UUIDs, rather than a simple integer primary key or composite key that is natural to the tabl…

I barely ever use uuids. As you say, it solves a fairly specific problem. Usually when you need to scale to multiple datacenters, it's easier and faster to add a datacenter column and starting using it with id as a composite key.

The only time I use UUIDs is when I need to pass that id out, but I want to hidr how many rows there are or the rate that they are being created. However, I usually use COMBs to prevent fragmentation.

Re: Design better databases

#172

Post by rusanu »

mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

I don't like this because it leads to the misleading assumption that it doesn't matter which value you refer (left or right), since they are equal. But once you consider collations is easy to see the fallacy:

   left.id = 'Something' COLLATE case insensitive
   right.id = 'SOMETHING' COLLATE case insensitive

   SELECT id FROM left JOIN right USING (id);
Will the value be 'Something' or 'SOMETHING'? Better avoid the surprise and be specific...

Re: Design better databases

#173

Post by adwf »

As a database guy this makes me feel good. From looking at a lot of these schemas, my job is not going to disappear anytime soon...

Most egregious example for me is probably the prevalence of a lot of "type" tables when a simple enum column would do. Or maybe the sheer number of UUIDs that are being thrown around.

I even saw a circular ID chain in one. Would be fun setting up foreign key constraints for that!

Re: Design better databases

#174

Post by Tenhundfeld »

ahachete wrote: post #166
Tenhundfeld wrote: post #163

Earlier quoted context omitted.

I didn't know about it, but I don't find those slides compelling, not at all. For example, their response to the "keys shouldn't change" is "what is CASCADE for then?". That seems like a naive view of data. It ignores things like integrating with other systems. If I export my data to an OLAP database, I can't just cascade that key change through it without manual effort. It also ignores managing historical data. Mayb…

Sure, there's CASCADE, but as you pointed out it has several drawbacks. That's why ideally natural keys should be immutable. That has many advantages over surrogate keys. There's a follow up post (posts) to this presentation: http://www.databasesoup.com/2015/03/primary-keyvil-reprised.... where many problems caused by the abuse of surrogate keys are illustrated. But the main point is simple: surrogate keys (abuse) is…

If a combination of data should always be unique in a table, you should put a unique constraint/index on those columns to ensure that data integrity. I think that's mostly unrelated to the surrogate key vs natural key debate.

Your point about immutable keys is important, I think. A "natural key" can mean different things, as I understand the term. You could call a person's social security number a natural key, which for the record is a bad idea as they can change . But you could also call the combination of user_id and reservation_id a composite natural key, which is immutable and not so bad in my book. I'd still use a surrogate key though.

It might seem like I care about this more than I actually do. I've just found over the years of writing systems that I fairly often regretted not having a surrogate key on a table, but I've never once regretted adding a surrogate key that ended up not being essential.

What I've seen happen is that the data model expands/changes and I need to reference a join table. I might just start out referencing it by the composite natural key. Then the model changes again, and now I need to add another piece of data that changes the natural key. For example, maybe the natural key was (user_id, reservation_id), but now it's (user_id, reservation_id, membership_type_id). Now I need to think about anywhere I referenced that join table by the natural key.

Re: Design better databases

#175

Post by ahachete »

Tenhundfeld wrote: post #174
ahachete wrote: post #166

Earlier quoted context omitted.

Sure, there's CASCADE, but as you pointed out it has several drawbacks. That's why ideally natural keys should be immutable. That has many advantages over surrogate keys. There's a follow up post (posts) to this presentation: http://www.databasesoup.com/2015/03/primary-keyvil-reprised.... where many problems caused by the abuse of surrogate keys are illustrated. But the main point is simple: surrogate keys (abuse) is…

If a combination of data should always be unique in a table, you should put a unique constraint/index on those columns to ensure that data integrity. I think that's mostly unrelated to the surrogate key vs natural key debate. Your point about immutable keys is important, I think. A "natural key" can mean different things, as I understand the term. You could call a person's social security number a natural key, which…

Unfortunately, UNIQUE is not enough to prevent the kind of errors I am talking about. They are only easily prevented if the PRIMARY KEY is a natural key.

I have seen many cases where concepts that are UNIQUE NOT NULL for a given business case are ignored as natural keys and surrogate keys are used instead. This results in "duplicated" records (from a business logic perspective) that often end up being manually cleaned, resulting in costs in the orders of 10s of thousands. Not funny.

The idea of a primary key is that you are guaranteed 0 or 1 results, but in the absence of UNIQUE and NOT NULL you may get more than one. That's a powerful abstraction. If your business logic determine that a given attribute or combination of attributes satisfies that condition, that deserves to be the row key.

If you rather use a surrogate key:

a) either you also have to enforce UNIQUE and specially NOT NULL on those attributes (apart from the PK) which is definitely not free lunch (extra indexes, more expensive joins, not benefiting from index-only scans)

b) or often someone forgets either the UNIQUE or NOT NULL on the attribute(s) that have to be constrained as such and data integrity is compromised.

Both cases are bad enough. What's the justification to systematically use surrogate keys then?

Re: Design better databases

#176

Post by level3 »

jonahx wrote: post #165
level3 wrote: post #124

Earlier quoted context omitted.

Some examples: In project management, tasks might have an assigner_id and assignee_id In transaction management, transactions might have a sender_id and receiver_id In sports, matches might have a team1_id and team2_id

> In transaction management, transactions might have a sender_id and receiver_id in normal form, shouldn't you have "transactions", "transaction_senders", and "transaction_receivers" anyway? similarly for the other cases? i know sometimes the simplicity of a single table is a valid choice, so your point still stands, but it does feel like the examples are cases of "well, if you're were doing the right way (TM), that…

These examples depend on the use case. If you are guaranteed to only have one sender and one receiver, then having them in the same table is already normal form.

Perhaps (?) a less controversial example is a table of flights between airports. Should a single flight have departure_ids and destination_ids? (assuming we treat legs as separate flights)

Or what about package delivery? Do we need sender_ids and receiver_ids there?

Re: Design better databases

#177

Post by moron4hire »

erpellan wrote: post #111
jessedhillon wrote: post #68

Earlier quoted context omitted.

When you have to join on composite PKs, it's easier to appreciate the parent's advice.

Some things that I'd love to see as options in rdbms: Enforced globally unique column names - natural joins on everything. Throw an exception if a query requires a table scan (bonus points if it printed the statement that would create the appropriate index). Translate all update and delete statements into inserts (enforced immutable data + versioning).

You can force the last one but just dropping the delete and update permissions from your role.

Re: Design better databases

#178

Post by koide »

ahachete wrote: post #175
Tenhundfeld wrote: post #174

Earlier quoted context omitted.

If a combination of data should always be unique in a table, you should put a unique constraint/index on those columns to ensure that data integrity. I think that's mostly unrelated to the surrogate key vs natural key debate. Your point about immutable keys is important, I think. A "natural key" can mean different things, as I understand the term. You could call a person's social security number a natural key, which…

Unfortunately, UNIQUE is not enough to prevent the kind of errors I am talking about. They are only easily prevented if the PRIMARY KEY is a natural key. I have seen many cases where concepts that are UNIQUE NOT NULL for a given business case are ignored as natural keys and surrogate keys are used instead. This results in "duplicated" records (from a business logic perspective) that often end up being manually cleane…

a) Premature optimization b) Implementation mistake

Using natural keys may offer better performance in certain cases, but does not protect you from b, as you might select a wrong key, or even forget about putting a PK on the table, for example.

Surrogates help you manage the ever changing and interconnected world: the immutable name of something that has been shared between systems is no longer immutable? This type of scenario is also very common and can cost thousands as well.

Which type of problem is more likely in your application? That's up to you to decide.

Re: Design better databases

#179

Post by jeremysmyth »

jpitz wrote: post #123
bmdavi3 wrote: post #120

Earlier quoted context omitted.

For queries returning results from a majority of rows in the table, won't a sequential table scan be faster than an index scan?

For non-clustered indices, yes.

and if the order is not important. Reordering large numbers of rows can be a very expensive operation.

Re: Design better databases

#180

Post by davvolun »

kbenson wrote: post #110
PeCaN wrote: post #108

Earlier quoted context omitted.

FFS, it's just reservation.reservation_name, it's not like he's parsing HTML with regular expressions or something. Like... big deal.

I'm not sure if this is humor going over my head, or seriousness smacking me in the face.

http://stackoverflow.com/questions/1732348/regex-match-open-...
Post reply on HN