4 ms·
I agree. However, re: document store, I've been bouncing Postgres around in my head for that: all required columns (ie, columns that always exist, or will be q
by DiabloD3 10y ago
I agree.
However, re: document store, I've been bouncing Postgres around in my head for that: all required columns (ie, columns that always exist, or will be queried on) are normal SQL columns, everything else is stashed in one or more hstore columns.
- StavrosK 10y ago> everything else is stashed in one or more hstore columns. This has the same problem as most NoSQL solutions: You don't get the benefit of a schema, and schemas are damn useful things.
- elmigranto 10y agoYou can define arbitrary constraints of any level of complexity with Postgres for any column, be it plain int, hstore, jsonb or anything else. Most of that will also be efficiently indexable out of the box.
- DiabloD3 10y agoNot everything needs a schema. The alternative I've also been bouncing around in my head is multiple tables: objects (rows in the "primary" table) have traits (rows in "secondary" tables with identical primary keys) that break out columns according to what they do. Having 9000 columns in a single table is a bit unwieldy.
- virtuabhi 10y agoI guess you are talking about database normalization. And it is part of schema design. https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
- coldtea 10y ago>Not everything needs a schema. Everything does. Data without schema are just noise. The real question is whether you'll enforce it at the DB level (and suffer from inflexibility) or enforce it at the code level (that, obviously, should know what field is what, and which fields are available, e.g. enforcing a schema at runtime) and get potentially corrupted data, management overhead, etc.
- danso 10y ago> Everything does. Data without schema are just noise. Love that concise adage. Will probably adopt it when explaining databases to novices.
- deleted 10y ago[deleted]
- DiabloD3 10y agoTo clarify, I meant exactly that: enforced at the DB level, or enforced in the code base.
- int_19h 10y agoThere are times when you don't care about the schema, though. E.g. when the data is mostly just pass-through, and you need to get it from here to there. Sometimes you need just a tiny bit of schema to know where "there" is (when that's encoded in the data), but you don't really care about the rest at all.
- st553 10y ago>Not everything needs a schema. Any good examples?
- fauigerzigerk 10y agoOne example would be ingesting structured or semi structured data from sources that you don't control. You may know some invariants, but much can change without notice. So you want to be able to work with the structure you have without preventing non-conforming data from entering your system. In some cases schema conformance is just delayed, in other cases it is never achieved completely or not even a goal.
- raarts 10y ago> one example would be ingesting data from structured or semi-structured sources that you don't control Can you give a more specific example?
- devdas 10y agoMetrics. A metric has a known source, a timestamp, a name and a value. It can also ship with any arbitrary number of descriptive fields. Similarly, events.
- geezerjay 10y agoThe known source might very well be expressed as a relation between two entities: a metric entity and a source entity. The source entity, more often than not, is also complemented with other data that needs to remain a part of the persistence layer.
- fauigerzigerk 10y agoFor instance, we need to retrieve statistical data on various macro economic indicators from various statistics offices and international organisations. There is considerable overlap in the fields they use but it's rarely exact and often you can't merge them because they do not refer to the exact same entity or the data uses incompatible units. It's impossible to properly model all of it before storing it because so much changes all the time and it's all noisy and partly broken. A similar thing happens when you retrieve data on securities and companies from various exchanges, from the SEC, from national registries all over the world or you try to include XBRL from different countries. And then you often have documents (like quarterly reports) that contain structured fields and tables but not in a formally specified syntax. You don't know exactly what fields will be in those documents before you parse them. So you parse the documents, store key/value pairs, and then you clean them up gradually. There are tons of situations like this in data integration. It's a never ending cleanup and merge process. You can use RDBMS for all of that but they're not always the best tool for the job (but they are still my preferred tool most of the time).
- felixgallo 10y agoEverything has a schema. There's no 'need' about it.
- StavrosK 10y agoYou might like this: https://github.com/stochastic-technologies/goatfish/ https://github.com/stochastic-technologies/goatfish/
- cwmma 10y agoyou can totally do this, you can even go one farther and put the value into a JSON column which can allow you to do queries on the value