4 ms·

Having helped "people run databases for their businesses" since the 1970's (yes I am a dinosaur), my approach has simplified dramatically into approaching all d

Having helped "people run databases for their businesses" since the 1970's (yes I am a dinosaur), my approach has simplified dramatically into approaching all database requirements as a collection of just 7 EAV (entity-attribute-value) tables [1] - one 3-field table for each field type: date, decimal, integer, text, time, varchar and then one for id/tenant. I then optimize the hardware for a few (very) long narrow tables, and shard as required. The beauty of this is that the database structure is all in data (where it should be) and I never have to alter the tables. As hardware has evolved, this approach serves my clients better, plus the turnaround time for changes is closer to the client instant gratification requirement. [1] https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...

magicalhippo 4y ago
Do you machine-generate views and stored procs for reading/writing data? How do you handle referential data? Like, order lines should have a foreign key linking them to the relevant order "head". Self-referential foreign key on the id table? Also, how's performance on a single node with a wide-ish "table"? Like, say I need to insert 1k rows x 30 columns, a fairly common, light load for us. edit: oh yeah, and what about indexing? We have a lot of indexes that touches 3-5 columns, and some even more, and almost all of those mix data types. For example we might have an (varchar, timestamp, varchar) index. Some of these are unique indexes, for consistency. These are crucial for normal operation.
- stevenjgarner 4y ago
  I appreciate your interest. On about my 2nd iteration of things I relied on MySQL Views for queries, but I felt that kind of defeated the purpose (in being truly data independent), so I wrote my own database and eav crud library (initially in PHP now refactored in production MVC in PHP 7.4 and a development version under way in node js). The library was pretty ugly at first for the SELECT but elegant and fast for INSERT, UPDATE and DELETE (as you're basically just doing up to 7 queries that are insert into / update / delete from table where parent_id=xyz). The ugliness with the SELECT initially was that I could not see past using giant IFNULL(MAX(CASE ... statements, which are not great on performance. As I refactored the library over the years, I realized more and more that increasing the scope and encompassing an entire content management system actually made things easier (still retaining the ability to INCLUDE any custom php file), as I could do the entirety of every web page in a single MySQL connection (open the database connection as the start of the page, doing the myriad queries the page required, then close the connection at the end of the page). In the general case, MySQL typically spends a lot more time making connections that it does executing queries. And then I realized the bulk of my SELECT statements were used to create grid tables from large data sets. Standard data grid table with CRUD + search + sort capabilities on every column and every row. I took a look at this from first principles and realized if I split the queries into two (one for the rows and another for the columns), I could eliminate the ugly IFNULL(MAX(CASE ... statements. In other words, you create one loop just pulling out the id for each row and inside that loop call another loop for all the grid columns based on that id needed to display that row. Assuming a grid of 30 rows with 20 columns say, that is 21 queries (but all over a single database connection). Of course you manage all the grid tables with pagination, so you might have 10,000 grid pages displaying 300,000 records 30 at a time on each page. If you present those grid tables in a usable manner (sorted by records most frequently accessed/updated, most recently accessed/updated etc), then statistically most of your users stay on the first grid page. And you manage the LIMIT statement to only ever get the 30 "virtual" records on the current page, whether in PHP or in JSON. Pretty fast! As far as indexing is concerned, well each of the 7 real MySQL tables was indexed, so yeah EVERY field is indexed whether you want it or not. Creating combined indexes between tables has not been attempted. So I got that approach performing nicely, then took it to another level putting the database schema itself inside the database. I could now build a "virtual" table from combining as many of the 7 fields as required for any given table definition. And just like a real MySQL table, you could join multiple "virtual" tables. After all an EAV database is about as normalized as you are ever going to get (one field per table, plus 2 indexes). Of course this is all done in data, so imagine a standard web table grid being used to Create/Retrieve/Update/Delete all the "virtual" table field definitions, labels, css values, etc.. This approach satisfied two of my business requirements - one need was to have a single framework to support a small number of clients with large datasets (e.g. millions of records, hundreds of tables). For them, I now had a tool where you do not need to be a programmer to create a simple web table grid of data. And where they needed hand-holding on more complex requirements, solutions could be deployed in a fraction of the time it would have taken me using traditional methods. The other need was to support a large number of clients (thousands) with very simple database requirements (e.g. for the business customers of an ISP). Now I had one database framework to do both. Doesn't satisfy every need, but it does one heck of a lot and takes care of all the low-hanging fruit. Like most developers who have been working for decades, it may have taken me that long to arrive at, but recreating it from scratch could be done very quickly.
  - magicalhippo 4y ago
    Thanks for the detailed response. I had guessed at your approach, and I wasn't too far off it seems. Moving the processing to the software level (rather than staying in the db layer) makes sense. A challenge we have is that for many of our UIs, we have very wide rows. Our main "offender" has over 100 fields on the main page, and that table also has several 3-4 level deep child tables. Would be interesting to see how it would perform using your approach. I might just have found another summer vacation project, just for fun... Not that I think it would work for us, like I said we rely on multi-column indexes for performance and consistency so that's probably a show-stopper right there. But I do like to try different approaches just for the experience.
remram 4y ago
You have basically recreated your own datastore on top of an existing DBMS, on top of which you have to create your own database. You will have to translate queries and reimplement many features like schema/data migrations, constraints, ... to get to a similar product with worse performance but (hopefully) better suited for your deployment strategy. Do what works for you I guess.
- stevenjgarner 4y ago
  For sure. Performance has not been anywhere near as big a challenge as my own productivity. This EAV approach has enabled me to do at least more than an order of magnitude more solutions for customers in the same time. I basically manage 2 EAV databases - a production one with the 7 tables (discussed) and a development one with the same 7 tables, where I will do imports, migrations, and such. One thing that is truly chaos is when you have hundreds of "virtual" EAV tables with 10's of millions of records (or more) in just 7 tables .... and the house of cards comes down for whatever reason (master-master replication failure, clustering failure etc). Gives you a whole new appreciation of the beauty of a simple flat file database.
davidthewatson 4y ago
I would agree generally with this approach having done a lot of experimental work around y2K with schemaless databases that predates the use of similar approaches in cloud. If you read the Wikipedia page, it refers to the use of json in this approach with postgresql but doesn’t mention the more modern bjson as EAV approach which addresses some concerns arising from the eav design.

magicalhippo 4y ago

Do you machine-generate views and stored procs for reading/writing data? How do you handle referential data? Like, order lines should have a foreign key linking them to the relevant order "head". Self-referential foreign key on the id table? Also, how's performance on a single node with a wide-ish "table"? Like, say I need to insert 1k rows x 30 columns, a fairly common, light load for us. edit: oh yeah, and what about indexing? We have a lot of indexes that touches 3-5 columns, and some even more, and almost all of those mix data types. For example we might have an (varchar, timestamp, varchar) index. Some of these are unique indexes, for consistency. These are crucial for normal operation.

stevenjgarner 4y ago
I appreciate your interest. On about my 2nd iteration of things I relied on MySQL Views for queries, but I felt that kind of defeated the purpose (in being truly data independent), so I wrote my own database and eav crud library (initially in PHP now refactored in production MVC in PHP 7.4 and a development version under way in node js). The library was pretty ugly at first for the SELECT but elegant and fast for INSERT, UPDATE and DELETE (as you're basically just doing up to 7 queries that are insert into / update / delete from table where parent_id=xyz). The ugliness with the SELECT initially was that I could not see past using giant IFNULL(MAX(CASE ... statements, which are not great on performance. As I refactored the library over the years, I realized more and more that increasing the scope and encompassing an entire content management system actually made things easier (still retaining the ability to INCLUDE any custom php file), as I could do the entirety of every web page in a single MySQL connection (open the database connection as the start of the page, doing the myriad queries the page required, then close the connection at the end of the page). In the general case, MySQL typically spends a lot more time making connections that it does executing queries. And then I realized the bulk of my SELECT statements were used to create grid tables from large data sets. Standard data grid table with CRUD + search + sort capabilities on every column and every row. I took a look at this from first principles and realized if I split the queries into two (one for the rows and another for the columns), I could eliminate the ugly IFNULL(MAX(CASE ... statements. In other words, you create one loop just pulling out the id for each row and inside that loop call another loop for all the grid columns based on that id needed to display that row. Assuming a grid of 30 rows with 20 columns say, that is 21 queries (but all over a single database connection). Of course you manage all the grid tables with pagination, so you might have 10,000 grid pages displaying 300,000 records 30 at a time on each page. If you present those grid tables in a usable manner (sorted by records most frequently accessed/updated, most recently accessed/updated etc), then statistically most of your users stay on the first grid page. And you manage the LIMIT statement to only ever get the 30 "virtual" records on the current page, whether in PHP or in JSON. Pretty fast! As far as indexing is concerned, well each of the 7 real MySQL tables was indexed, so yeah EVERY field is indexed whether you want it or not. Creating combined indexes between tables has not been attempted. So I got that approach performing nicely, then took it to another level putting the database schema itself inside the database. I could now build a "virtual" table from combining as many of the 7 fields as required for any given table definition. And just like a real MySQL table, you could join multiple "virtual" tables. After all an EAV database is about as normalized as you are ever going to get (one field per table, plus 2 indexes). Of course this is all done in data, so imagine a standard web table grid being used to Create/Retrieve/Update/Delete all the "virtual" table field definitions, labels, css values, etc.. This approach satisfied two of my business requirements - one need was to have a single framework to support a small number of clients with large datasets (e.g. millions of records, hundreds of tables). For them, I now had a tool where you do not need to be a programmer to create a simple web table grid of data. And where they needed hand-holding on more complex requirements, solutions could be deployed in a fraction of the time it would have taken me using traditional methods. The other need was to support a large number of clients (thousands) with very simple database requirements (e.g. for the business customers of an ISP). Now I had one database framework to do both. Doesn't satisfy every need, but it does one heck of a lot and takes care of all the low-hanging fruit. Like most developers who have been working for decades, it may have taken me that long to arrive at, but recreating it from scratch could be done very quickly.
- magicalhippo 4y ago
  Thanks for the detailed response. I had guessed at your approach, and I wasn't too far off it seems. Moving the processing to the software level (rather than staying in the db layer) makes sense. A challenge we have is that for many of our UIs, we have very wide rows. Our main "offender" has over 100 fields on the main page, and that table also has several 3-4 level deep child tables. Would be interesting to see how it would perform using your approach. I might just have found another summer vacation project, just for fun... Not that I think it would work for us, like I said we rely on multi-column indexes for performance and consistency so that's probably a show-stopper right there. But I do like to try different approaches just for the experience.

remram 4y ago

You have basically recreated your own datastore on top of an existing DBMS, on top of which you have to create your own database. You will have to translate queries and reimplement many features like schema/data migrations, constraints, ... to get to a similar product with worse performance but (hopefully) better suited for your deployment strategy. Do what works for you I guess.

stevenjgarner 4y ago
For sure. Performance has not been anywhere near as big a challenge as my own productivity. This EAV approach has enabled me to do at least more than an order of magnitude more solutions for customers in the same time. I basically manage 2 EAV databases - a production one with the 7 tables (discussed) and a development one with the same 7 tables, where I will do imports, migrations, and such. One thing that is truly chaos is when you have hundreds of "virtual" EAV tables with 10's of millions of records (or more) in just 7 tables .... and the house of cards comes down for whatever reason (master-master replication failure, clustering failure etc). Gives you a whole new appreciation of the beauty of a simple flat file database.

davidthewatson 4y ago

I would agree generally with this approach having done a lot of experimental work around y2K with schemaless databases that predates the use of similar approaches in cloud. If you read the Wikipedia page, it refers to the use of json in this approach with postgresql but doesn’t mention the more modern bjson as EAV approach which addresses some concerns arising from the eav design.