Was wondering how difficult is to code a UI like this? i.e. SVG with draggable boxes with the connections/lines?
Design better databases
161–170 of 182 posts
Re: Design better databases
#162Earlier quoted context omitted.
I never understood why the thing named "natural join" don't use relationships to determine the joining columns. Does not look natural for me.
Indeed, this is also what always annoyed me. I suspect that this has to do with the fact that NATURAL JOIN is intended to work for sub-SELECTs and VIEWs, too. On those, it is quite hard and error-prone to determine "foreign key" equivalents. Also note that restricting NATURAL JOINs to tables wouldn't be a good solution here, either, because that would prevent you from ever JOINing more than 2 tables: When JOINing 3 t…
You get the same sort of problems jessedhillon was talking about.
That is, you lose absolutely nothing - just changes its semantics.
Re: Design better databases
#163Earlier quoted context omitted.
Yeah, I hear that. I also tend to always use surrogate keys. It can have performance implications, but that's typically not my biggest concern. EDIT: I guess that doesn't really address your point of needing to join on a composite natural key though.
Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)
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. Maybe I don't want that natural key update cascaded everywhere. In their example of hotel room number, if that public-facing number changed (which does happen), I wouldn't necessarily want that cascaded everywhere. For example, I might need past reservations/invoices from before the change to keep the old room number, to match all correspondence with the guest, etc. Okay, so maybe you don't automatically cascade everywhere, but then you need some way to link that old room number and the new room number in all of your reporting.
Bleh. That's a lot of headache that can be greatly minimized with a surrogate key, for very little drawback in my experience. Sure, I can imagine scenarios where the performance impact or additional storage could actually be a real negative, but for your average CRUD app, I think surrogate keys add way, way more value than cost.
Re: Design better databases
#164Earlier quoted context omitted.
Why should UUIDs not be exposed to users? Because of the their unwieldy look, or because of security concerns? I ask because I have an app where previously I was accessing a particular data point via pk, and the user saw the pk in the url bar. But it could expose user/site data as any user could access that pk, or just guess at the next sequentially generated pk. I switched to uuid and now it's a little ugly in the u…
> But it could expose user/site data as any user could access that pk, or just guess at the next sequentially generated pk. Using a UUID instead of a sequentially rolling integer ID isn't solving your problem, you're just doing security through obscurity. The real solution is implementing real authentication & authorization - not making the key harder to guess.
> Using a UUID instead of a sequentially rolling integer ID isn't solving your problem, you're just doing security through obscurity.
A common sentiment, but not true if your id is cryptographically random. It amounts to capability security which is the right approach to security if used comprehensively.
Re: Design better databases
#165Earlier quoted context omitted.
Genuinely curious, what's the use-case for this?
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 wouldn't happen"
Re: Design better databases
#166Earlier quoted context omitted.
Do you know SSSKA? Society to Stop Surrogate Key Abuse: http://www.slideshare.net/PGExperts/keyvil-lightning-talk?qi... ;)
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 an easy way to data corruption. Not in the sense of durability as in ACID, but in the sense of humans (manually or by bugs in the software) duplicating information that is not enforced to be unique thanks to using non-sense surrogate keys as the uniqueness criteria.
Re: Design better databases
#167Earlier quoted context omitted.
With aliases that first expression might be: c.id = p.id That seems like an easy error to make...
> With aliases that first expression might be: c.id = p.id Which is why I don't use aliases, so that future me doesn't have to wonder what the hell he wrote a few months ago.
Once an application has been alive for more than a few months, I find it often is subject to self-joins and other query approaches which pretty much beg for aliases in the name of legibility.
Re: Design better databases
#168Looking 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 table. People say it's just an id, who cares? But I say people making these kinds of decisions are probably making other poor choices backed by something they read and don't understand.
Here's a great article on locality and the need for uniqueness and why UUIDs should only be introduced when needed and to solve very specific distributed systems problems. The reality is almost all applications will never need to have multiple concurrent writers.
Re: Design better databases
#169Earlier quoted context omitted.
> With aliases that first expression might be: c.id = p.id Which is why I don't use aliases, so that future me doesn't have to wonder what the hell he wrote a few months ago.
Once an application has been alive for more than a few months, I find it often is subject to self-joins and other query approaches which pretty much beg for aliases in the name of legibility.
subject to self-joins and other query approaches
Which means that the original argument about reserveration_id vs id doesn't matter since it's likely that both table and column aliases would be needed anyways.
Re: Design better databases
#170Earlier 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…
shouldn't you have "transactions", "transaction_senders", and "transaction_receivers"
were doing the right way(TM), that wouldn't happen
Having a separate transaction_senders and transaction_receivers wouldn't be the right way(TM) unless it's possible to have multiple senders/receivers. If a transaction can only have 1 sender/receiver then normalization is complete when the sender/receiver data is removed from 'transactions' table and replaced with the sender_id and receiver_id columns.
When introducing transaction_senders/transaction_receivers tables without further constraints, it would be immediately possible to have multiple transaction senders and multiple transaction receivers for a single transaction, which is likely an error.