It's only a technology debt
-
@boomzilla said:
@Ben L. said:
I find it hard to believe it's a non-WTF™ to have a database where multiple unrelated things share the same table and are only differentiated by something at least three joins away (table Snoofle is talking about → some table that links → table it talks about → [...] → clients table)
This is an exciting day for us all! Let me introduce you to...Database Normalization.
It depends. Database normalization is a tradeoff; you usually get worse time performance (not noticeable except for very large databases) in exchange for better disk space usage (which sometimes, but not always, makes up for the performance loss by requiring fewer disk reads/writes) and fewer developers driven mad by the very system they helped create.
In this case it sounds like a) their database is already normalized, and b) it's not helping the problem at all, and in fact is possibly making it worse (by increasing the time needed to filter the data).
-
@curtmack said:
@boomzilla said:
@Ben L. said:
I find it hard to believe it's a non-WTF™ to have a database where multiple unrelated things share the same table and are only differentiated by something at least three joins away (table Snoofle is talking about → some table that links → table it talks about → [...] → clients table)
This is an exciting day for us all! Let me introduce you to...Database Normalization.
It depends. Database normalization is a tradeoff; you usually get worse time performance (not noticeable except for very large databases) in exchange for better disk space usage (which sometimes, but not always, makes up for the performance loss by requiring fewer disk reads/writes) and fewer developers driven mad by the very system they helped create.Thank you for missing the point. As Thomas Sowell said, "There are no solutions, only trade offs." Obviously, performance is a problem for snoofle's case, but normalization is the answer to BenL's apparent ignorance, and the level of normalization may not be a WTF in snoofle's case. We certainly don't know enough details to conclude that.
-
@curtmack said:
It depends. Database normalization is a tradeoff; you usually get worse time performance (not noticeable except for very large databases) in exchange for better disk space usage (which sometimes, but not always, makes up for the performance loss by requiring fewer disk reads/writes) and fewer developers driven mad by the very system they helped create.
Bunk. I've only heard lousy programmers make that argument to justify why their database "designs" were steaming piles of dung. If you don't normalize, you might as well put your fucking database in an Excel sheet, because that's basically what you're using that expensive sophisticated RDMS to do.
I agree that you can over-normalize, but that's a different argument.
-
@this_code_sucks said:
@Zemm said:
@Xyro said:
The query takes about two hours to run? Hmm. Schedule the query to be run in a loop. Store the results. When the clients ask for the query to be run, wait a second, then return the potentially-two-hours-old results.
That was my first thought too. The ultimate de-normalisation of the data!
+1
this
-
@beginner_ said:
Wow, it only took you five days to type a word. Community Server must have some magical optimization or something.@this_code_sucks said:
@Zemm said:
@Xyro said:
The query takes about two hours to run? Hmm. Schedule the query to be run in a loop. Store the results. When the clients ask for the query to be run, wait a second, then return the potentially-two-hours-old results.
That was my first thought too. The ultimate de-normalisation of the data!
+1
this
-
@Ben L. said:
Wow, it only took you five days to type a word. Community Server must have some magical optimization or something.
Maybe he's using Lotus Notes, Domino or Twitter.
now let's wait a few days and see if this post had enough keywords to awaken the Yellow Menace
-
Unfortunately I do not fully understand Oracle's RDBMS or your particular database. In SQL we now have filtered indexes, that could truly help on some of your tables without the huge duplication. I understand that Oracle has function-based indexes that can do something similar. It may still not get you to where you need to be.
Then again, they really should not expect you to have something that runs in one second if they didn't give you that requirement in the first place, but I have been in your position where the "contract says X so we need to deliver it". Of course, in my case, the contract always said we would have X in 6 months after signing. The developers were told of this requirement 5 1/2 months after signing.
-
@Ben L. said:
Multiple clients share the same [b]rows[/b] in the database?
@Ben L. said:a database where multiple unrelated things share the same [b]table[/b]
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@DaveK said:
@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
What is that "row" thing you talk about? I guess this is some kind of bastardized concept vaguely related to a tuple? Then how do you call that when it's a column-oriented database? A stack?
-
@DaveK said:
rows = entries, tables = sets of entries. Both of my statements use the correct words.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@Ben L. said:
@DaveK said:
Wait, so now you're claiming that those are two semantically different statements, rather than two formulations of the same statement? Pretty goddamn misleading of you to use the second one in a supposed recap-of-the-conversation-so-far in that case.
rows = entries, tables = sets of entries. Both of my statements use the correct words.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@DaveK said:
@Ben L. said:
@DaveK said:
Wait, so now you're claiming that those are two semantically different statements, rather than two formulations of the same statement? Pretty goddamn misleading of you to use the second one in a supposed recap-of-the-conversation-so-far in that case.
rows = entries, tables = sets of entries. Both of my statements use the correct words.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
Disclaimer: I do not condone violence
-
@Speakerphone Dude said:
@DaveK said:
FTFY@Ben L. said:
@DaveK said:
Wait, so now you're claiming that those are two semantically different statements, rather than two formulations of the same statement? Pretty goddamn misleading of you to use the second one in a supposed recap-of-the-conversation-so-far in that case.
rows = entries, tables = sets of entries. Both of my statements use the correct words.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
Disclaimer: I do condom violence
-
@DaveK said:
There's a reason Codd came up with 'tuple' and 'relation' when describing what is colloqually (still) known as rows/records and tables in relational databases; it's that they weren't precisely defined 'techincal terms,' and could be ambigious.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@PJH said:
@DaveK said:
That doesn't actually undermine what I said. Maybe you should have posted a llama meme.
There's a reason Codd came up with 'tuple' and 'relation' when describing what is colloqually (still) known as rows/records and tables in relational databases; it's that they weren't precisely defined 'techincal terms,' and could be ambigious.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@DaveK said:
Are you seriously proposing that multiple unrelated installations of a program should access the same rows in the same database and that all data should be put into the same table, or split up in a non-intuitive way? Because that's the only reason you would continue arguing after I made my meaning clear.@PJH said:
@DaveK said:
That doesn't actually undermine what I said. Maybe you should have posted a llama meme.
There's a reason Codd came up with 'tuple' and 'relation' when describing what is colloqually (still) known as rows/records and tables in relational databases; it's that they weren't precisely defined 'techincal terms,' and could be ambigious.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
-
@Ben L. said:
Are you seriously proposing that multiple unrelated installations of a program should access the same rows in the same database and that all data should be put into the same table, or split up in a non-intuitive way? Because that's the only reason you would continue arguing after I made my meaning clear.
Just to be clear. This is obviously unrelated to snoofle's issue, which is possibly what confused the people reading your posts.
-
@Ben L. said:
Except you haven't made your meaning clear. I wasn't joking when I asked you if the two statements I quoted were supposed to be different claims or restatements of the same claim, it really isn't obvious.the only reason you would continue arguing after I made my meaning clear.
-
Rawr!
-
@PJH said:
@DaveK said:
There's a reason Codd came up with 'tuple' and 'relation' when describing what is colloqually (still) known as rows/records and tables in relational databases; it's that they weren't precisely defined 'techincal terms,' and could be ambigious.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
A tuple is nothing else than a bunch of key/value pairs. From an ontological point of view, this would mean that a PHP associative array can be called "rows" and an array of arrays can be called a table.
Yet
SELECT * FROM $_GETdoesn't work, I'll file a report on bugs.php.net
-
@da Doctah said:
@snoofle said:
It turns out that the developers were never told of a requirement, promised by the salesman, and agreed to in the contract by corporate brass, that said report would finish in ONE SECOND.
How long has this site been running that we're just now coming to the realization that TRWTF is marketing people?
Is it marketing people falsely advertising the product, or is it a salesman that's committed to a specification without a prior feasibility study? Or perhaps a designer that missed this specification out when translating business requirements into functional designs?
The info was captured and agreed but was dropped somewhere along the chain. I agree that the first WTF is sales selling something without understanding the cost of fulfilling that requirement, but a second was omitting it from the final realisation - flagging up the actual cost investment associated with this specification (and making the sales guy look stupid when he begins to see his ROI point vanish further into the distance).
-
@ASheridan said:
@blakeyrat said:
BTW, the product I used to work for had a "1 database per client" design which I've always strongly believed in for various reasons. It's stupid to mix data from different clients into a single gigantic database even if that does mean to save 6 MB on some shared lookup data. Ugh.
That might work, but when you've got essentially the same service that a bunch of clients are using then updating the schema as the application evolves will be a pain if the steps involved are even slightly more involved than adding a new field, multipled once for each duplication.The flipside is schema updates affect all customers of that schema - whether or not they want it. By having per-customer segregation, it is possible to limit updates to specific customers.
I would also add that schema changes are probably scripted and deployed between testbed and production, so repeating the same deployment across multiple schema should cost additional time but not much more effort. It would extend the release window, possibly bringing some scheduling issues... but the benefits seem to outweigh the drawbacks. At least, for the systems I've worked on - YMMV.
-
@Speakerphone Dude said:
@PJH said:
You can easily do this in .NET however using the LINQ to Objects syntax@DaveK said:
There's a reason Codd came up with 'tuple' and 'relation' when describing what is colloqually (still) known as rows/records and tables in relational databases; it's that they weren't precisely defined 'techincal terms,' and could be ambigious.@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
If you're going to try and talk knowledgeably about databases, you really ought to be clear on the difference between a "row" and a "table". It's not mere pickiness or semantic quibbling; these are precisely defined technical terms, and if you use the wrong one, you end up talking gibberish.
A tuple is nothing else than a bunch of key/value pairs. From an ontological point of view, this would mean that a PHP associative array can be called "rows" and an array of arrays can be called a table.
Yet
SELECT * FROM $_GETdoesn't work, I'll file a report on bugs.php.net
-
@Ben L. said:
Multiple clients share the same rows in the database?
@Ben L. said:a database where multiple unrelated things share the same table
What about a database where multiple unrelated things share the same row, which is shared by multiple clients? And that's common in that particular table?
Yeah, I left that employer as fast as I could. It was approaching two decades ago, so no real details. I left within about three weeks of discovering that bit, so I didn't have a lot of time to really come to embrace those details. Sorry. (That wasn't actually the bit that made me leave; I was already on my way out.)
-
@tgape said:
What about a database where multiple unrelated things share the same row, which is shared by multiple clients? And that's common in that particular table?
Surely there's got to be some relationship between them - as the row is referenced by a PK, that PK has a shared meaning to those clients retrieving that data....?
Granted that it may not be used or interpreted in the same way, but I can't see how they're completely isolated.
