h a l f b a k e r yThe halfway house for at-risk ideas
add, search, annotate, link, view, overview, recent, by name, random
news, help, about, links, report a problem
browse anonymously,
or get an account
and write.
register,
|
|
|
Imagine a deliberately limited database that can only run
queries that you specify that you will run.
From this information (and limitation) it can use this
information to pick data structures that maximize
performance whereas a traditional database uses the
same
tabular format regardless
of the query.
For example, if a query that you have stored uses the
whole table, it's more efficient to use a column
orientated
database.
Or if you only ever do simple joins between two tables,
such as `select people.firstname, orders.name from
people
join orders on people.id = orders.person_id where
people.firstname = 'chronological'`, you could store
orders
by person.
or if you have a query that is like select people from
orders where name = "chronological" you can have a file
per person on disk.
FTR, Postgres is efficient enough with its tabular
database, but I think this idea has some merit.
I think this (materialized views), plus some triggers*, will get you that.
https://en.wikipedi...i/Materialized_view
*I mean some relatively simple triggers - with arbitrarily complex database triggers you can do anything, but probably shouldn't. [pertinax, Apr 12 2020]
Putt's Law
https://en.wikipedi...ccessful_Technocrat
Relevant to user-maintained systems [8th of 7, Apr 21 2020]
[link]
|
| |
So, adaptive organizational structure ? Bit of a pain on a large DB if it starts to thrash. |
|
| |
The queries are set in advance so the database knows how
to lay out the data. |
|
| |
If queries need to be changed then yes, that would cause
thrashing as data has potentially to be rewritten into a new
format. |
|
| |
That's what's going to make it noncommercial. In commercial environments, where the money's made, inherent flexibility and the capacity to make changes quickly and cheaply are primary requirements. |
|
| |
The idea assumes a static and tighty-bounded system, which are rare in the real world. |
|
| |
Many users can easily generate a new query, perhaps based on an existing query. Redesigning a database requires constant SysAdmin input, downtime, and the potential to introduce errors and/or lose data. |
|
| |
Businesses won't use such a system, hence while it is technically possible, and interesting (though such concepts have been raised in academia since the '60s) it's not viable as a product. |
|
| |
pertinax Materialized views are still based on the tabular
data format that the database uses. |
|
| |
You might as well just use a spreadsheet. Lots of _lusers do that. |
|
| |
I really think there should be a spreadsheet backed by a
Postgres database. It would be fast. |
|
| |
How fast does a spreadsheet need to be ? They're a clunky and inefficient way of manipulating large data sets (tho very useful for small ones) and at small sizes they're acceptably fast even on modestly powerful systems. |
|
| |
I want a fusion of databases and spreadsheets. |
|
| |
The ease of defining relations with a spreadsheet and
formulas. |
|
| |
The performance of a database. |
|
| |
Would be awesome. Imagine an order system implemented
this way. |
|
| |
... and then let the users maintain it ? |
|
| |
Have you ever tried hypnotic regression ? You should. You really should. We guess that in a past life, you were a Kamikaze pilot ... |
|
| |
//a fusion of databases and spreadsheets// |
|
| |
Devices exist to fuse the two. |
|
| |
The resulting "solutions" tend to produce maintenance
nightmares. |
|
| |
Unless it's a single-user design where the designer, user and maintainer are the same individual, it's like juggling running chainsaws; fascinating to watch, because it's only ever a matter of time before it goes horribly wrong ... |
|
| |
That's true, but the administrators will inevitably tend to create the queries that they perceive as most useful to them, rather than those that the users perceive as important*. |
|
| |
The Admins want to keep the database running well; that is their goal. They will subordinate that to other tasks, since if the database degrades or fails they will be criticised. If the users merely get slow service, or they query they want is not created for them in an acceptable timeframe, the criticism will be within accepted bounds and can be deflected by resort to the List of Standard Excuses. |
|
| |
*Since users are not in fact important unless they have sufficient authority to make the Admins lives unpleasant, they are by definition unimportant. Better, those who have such authority almost always lack the technical understanding of the issues and can almost always be fobbed off with a bit of carefully considered Technobabble. |
|
| |
Actually, no; there is a sweet spot. It can be found where
1. there are reasonably intelligent, responsible "power
users"
taking responsibility for the spreadsheets themselves,
(and not
the databases or the "fusion" mechanism) and
2. (this is important) the data-flow is almost entirely one-
way, viz.,
from database to spreadsheet, and *not* vice versa.
Given this
"look, don't touch" limitation, you can get away with
software
engineering practices which would be disastrous
otherwise. |
|
| |
// you can get away with software engineering practices which would be disastrous otherwise. // |
|
| |
That sounds like the strategy of someone who already has a totally reliable exit plan from the organization, through retirement, promotion, or departure for a better post elsewhere... |
|
| |
Spot on, [8th]; I did tiptoe away from that organisation
about ten years ago. |
|
| |
And were no doubt sufficiently distanced from it not to get spattered when the excrement encountered the air-handling system ? |
|
| |
See also Putt's Law <link> |
|
| |
Actually, the excrement never did hit the air-handling
system. |
|
| |
The engineering practices *would have been* disastrous with
a different solution architecture but, with the actual
solution architecture, they were OK. |
|
| |
<Peers into Crystal Ball/> |
|
| |
We see... a small, tightly knit team of capable and motivated technical staff, and a management with just enough wit to realize that although they don't understand the issues, the team does, and the best and safest policy is not to interfere. |
|
| |
Genuine conversation (not a copy from Dilbert):
|
|
| |
"Do we have a solution for this, then ?"
|
|
| |
"I'm not going to tell you."
|
|
| |
"But I'm the manager. Why not ?"
|
|
| |
"If I tell you, it might encourage you to make decisions in a futile attempt to modify the outcome of events, which is unacceptable. Just sign this requisition. The problem can then be considered solved."
|
|
| |
(He was, by and large, a very good manager). |
|
| |
No, but bless you for trying. |
|
| |
When did you last have that ball serviced? |
|
| |
This halfbakery idea is not a fusion of spreadsheets and
databases. That was an offhand idea. |
|
| |
So, the core idea was for proprietary file formats, optimized
for a limited set of use cases only? |
|
| |
> So, the core idea was for proprietary file formats,
optimized for a limited set of use cases only?
— pertinax |
|
| |
Kind of. For a given query, there is a data structure that is
optimised for answering that question. |
|
| |
You could model Halfbakery as a set of tables in a SQL
database. Flat files and directories or you could have objects
in memory. |
|
| |
I think proprietary file formats for limited use cases are WKTE.
As the app develops, you can waste a lot of effort reinventing
the wheels of referential and transactional integrity - and, if you
fail to reinvent those wheels correctly, you get some nasty bugs. |
|
| |
There isn't a database that exists that accepts queries and
uses them to schedule data storage format. |
|
| |
So, the idea is now for an application which re-configures its
own persistence layer dynamically in response to queries? |
|
| |
Correct, except you load the queries up in advance of running
them, indeed before you put the data in the database. |
|
| |
So you have a database that is fast for the queries that you
load it with. |
|
| |
That sounds very much like the 4GLs that were popular in the early 90s. |
|
| |
I imagine providing a histogram with your queries. |
|
| |
If most of your queries are a simple join, you can optimise for
that case in your data model. |
|
| |
Fine as a teaching aid, but real-world situations are much more complex, hence the reason databases are the way they are. |
|
| |