71–80 of 264 posts
Earlier quoted context omitted.
If you aren't implementing your own tabular database on top of your existing tabular database, you aren't 2018 enough :)
I'm changing the game by code generating a tabular database on top of an eventualy consistent store. 2020 here we come!!!
I don't quite get this. How fast was running this query: Select loyaltyMemberID from table WHERE gender = x AND (age = y OR censor = z) Why the random complexity with individual unions and a group? Of course that's going to be dog slow. Sure, the filters can be arbitrary but with an ORM it's really really simple to build them up from your app code. The Django ORM with Q objects is particularly great at this. Obviousl…
The user data is most likely in rows instead of columns. Instead of having id, name, age, gender 1213, fake, 60, female they would have property_id, user_id, value 1 (assume age), 1213, 60 2 (gender), 1213, female This gives them the freedom to add more properties to the user without always having to add a column to the users table. When querying the database you'll have to do unions or joins.
I think you're right. Oh ... my ... god ...
I wish I could say this is the worst example of a database schema I've ever seen, but it isn't.
Technology cycle:
X gets invented -> idiots abuse it -> X "is bad" -> Y (strictly worse than X) is "so much better" -> idiots abuse it -> Y "is bad" -> ...
Earlier quoted context omitted.
> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)
Do you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is worth storing (like calculations) and how to choose which data to group in tables.
If it’s transactional, I recommend keeping calculations only if you need to access summarized data frequently. For example, if you are tracking inventory by storing the history of transactions that occur into and out of inventory, it’s trivial to find out how much of each item you have in stock at any point in time by doing a sum of the change in quantities for each item type up to that point.
If you were usually interested in the “current” count, it would be expensive to perform this sum every time, so instead you could keep a separate table for calculating the running total of inventory per item and referring to that. Keep this table up to date through the use of triggers on insert events (Note that your log of inventory transactions would thus be an immutable stream of events)
An example of something not worth storing is derived data that is a combination of separate columns in a table. For most queries it’s probably trivial to be lazy and wait to perform such a calculation until you actually need it. If you still want to have a ready made “table” that has all the computations you want already entered as columns for easy querying, use a view. If you find yourself making liberal use of views, you’re on the right track.
If you are using a separate data warehouse for data analysis, then precompute and denormalize as much as you can.
*Disclaimer: most of what I’m talking about is from a Postgres perspective.
Earlier quoted context omitted.
Yeah, sadly, this is not too much of an exaggeration. I've worked on teams that insisted they needed DynamoDB, because, well, Dynamo is for "Big Data", and they certainly wouldn't work somewhere that had "Small Data"! Replace the buzzwords/products as applicable; you could actually probably just scramble them and it'd work just as well, since someone out there thinks "RabbitMQ means Web Scale", etc. SQL databases are…
Question, though... If your columns have types, how can you encrypt them using custom keys for each one? Is it possible? I want the keys to reside on the client and search for encrypted data. Basically single row lookups at a time.
Earlier quoted context omitted.
> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)
Do you have any recommendations for resources to learn best database practices? I'm currently designing my first database and I'm not sure what information is worth storing (like calculations) and how to choose which data to group in tables.
Earlier quoted context omitted.
The user data is most likely in rows instead of columns. Instead of having id, name, age, gender 1213, fake, 60, female they would have property_id, user_id, value 1 (assume age), 1213, 60 2 (gender), 1213, female This gives them the freedom to add more properties to the user without always having to add a column to the users table. When querying the database you'll have to do unions or joins.
Oh gosh this pattern. The first time I encountered it was in my first job where we used Magento. Super flexible. Also super slow. Does anyone have any advice how to make a db design like this work faster? Generally I thought when data is arranged like this it might be a prime candidate for document based storage. But I'm no dba so I have no idea if that would be correct.
What we have here is a classic dbms problem and no one at Movio seems to know how to deal with that. Instead of migrating from Mysql to something serious (Postgres) they move to some columnar DB no one has heard of. Nevermind that postgres and a reasonably priced DBA and a little thought put into their data model/queries could probably handle all their issues.
Sorry for the snark, cheers on a successful product.
Earlier quoted context omitted.
The user data is most likely in rows instead of columns. Instead of having id, name, age, gender 1213, fake, 60, female they would have property_id, user_id, value 1 (assume age), 1213, 60 2 (gender), 1213, female This gives them the freedom to add more properties to the user without always having to add a column to the users table. When querying the database you'll have to do unions or joins.
Oh gosh this pattern. The first time I encountered it was in my first job where we used Magento. Super flexible. Also super slow. Does anyone have any advice how to make a db design like this work faster? Generally I thought when data is arranged like this it might be a prime candidate for document based storage. But I'm no dba so I have no idea if that would be correct.
I don't quite get this. How fast was running this query: Select loyaltyMemberID from table WHERE gender = x AND (age = y OR censor = z) Why the random complexity with individual unions and a group? Of course that's going to be dog slow. Sure, the filters can be arbitrary but with an ORM it's really really simple to build them up from your app code. The Django ORM with Q objects is particularly great at this. Obviousl…
The user data is most likely in rows instead of columns. Instead of having id, name, age, gender 1213, fake, 60, female they would have property_id, user_id, value 1 (assume age), 1213, 60 2 (gender), 1213, female This gives them the freedom to add more properties to the user without always having to add a column to the users table. When querying the database you'll have to do unions or joins.