This is called the Observation Pattern. Three objects, for the example Book Title=”Gone with the Wind” Author=”Margaret Mitchell” ISBN = ‘978-1416548898’ Cat Name=”Phoebe” Color=”Gray” TailLength = 9 ‘inch’ Beer Bottle Volume = 500 ‘ml’ Color=”Green” This is how tables may look like: Entity EntityID Name Description 1 ‘Book’ ‘To read’ 2 ‘Cat’ ‘Fury cat’ 3 … Read more
(Tested in PostgreSQL 9.something) Identify the rid and timestamp. select rid, max(timestamp) as ts from test group by rid; 1 2011-04-14 18:46:00 2 2011-04-14 14:59:00 Join to it. select test.pid, test.cost, test.timestamp, test.rid from test inner join (select rid, max(timestamp) as ts from test group by rid) maxt on (test.rid = maxt.rid and test.timestamp = … Read more
The difference between 3NF and BCNF is subtle. 3NF Definition A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in 3NF if for each functional dependency X ⟶ A in R, at least one of the following … Read more
Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database). It means you can’t physically create a record that doesn’t fulfill relation. It’s just a way to be more safe.
The ixset library (or ixset-typed, a more type-safe version) will help you with this. It’s the library that backs the relational part of acid-state, which also handles versioned serialization of your data and/or concurrency guarantees, in case you need it. The Happstack Book has an IxSet tutorial. The thing about ixset is that it manages … Read more
A star schema really lies at the intersection of the relational model of data and the dimensional model of data. It’s really a way of starting with a dimensional model, and mapping it into SQL tables that somewhat resemble the SQL tables you get if you start from a relational model. I say somewhat resemble … Read more
The answer is unfortunately: Your consideration is completely right in every point. You have to store Nodes (Vertices) in one table, and Edges referencing a FromNode and a ToNode to convert a graph data structure to a relational data structure. And you are also right, that this ends up in a large number of lookups, … Read more
The viewpoint of the CTO of a company that extensively uses RDF Triplestores commercially: Schema flexibility – it’s possible to do the equivalent of a schema change to an RDF store live, and without any downtime, or redesign – it’s not a free lunch, you need to be careful with how your software works, but … Read more
Key-value, heirarchical, map-reduce, or graph database systems are much closer to implementation strategies, they are heavily tied to the physical representation. The primary reason to choose one of these is if there is a compelling performance argument and it fits your data processing strategy very closely. Beware, ad-hoc queries are usually not practical for these … Read more
SELECT * FROM (SELECT [UserID] FROM [User]) a LEFT JOIN (SELECT [TailUser], [Weight] FROM [Edge] WHERE [HeadUser] = 5043) b ON a.UserId = b.TailUser