You’re making a common mistake of database design, storing name in one column and value in another column. This is not a relational database design. Each attribute should be named by the column name. Color, pages, shirt size, publish date, should be column names. If each product type has a distinct set of attributes, there … Read more
TL;DR “Superkey” is a RM (Relational Model of Data) term. There’s no standard use in SQL. The superkeys of an SQL table might reasonably informally be said to be the column sets that you could declare primary key or unique not null, plus maybe {} when a table holds at most one row (although you … Read more
Assuming you have a relation, A, with a single attribute, ‘a’ (reducing a more complex relation to this is a simple task in relational algebra, I’m sure you got this far), so now you want to find the maximum value in A. One way to do it is to find the cross product of A … 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
Generally, there are two strategies to handle a situation like this: Use Exclusive FKs Essentially, each of the possible parent tables will have its own, separate foreign key in the child table, and there is a CHECK enforcing exactly one of them is non-NULL. Since FKs are only enforced on non-NULL fields (meaning, when a … Read more
This is a bad design. Just make a 2-column primary key, and 2-column foreign keys to it. This is a fundamental anti-pattern called “encoding information in keys” which (thereby) are called “smart”, “intelligent” or “concatenated” keys. A good key is a “dumb” key. Eg:: Despite it now being easy to implement a Smart Key, it … Read more
A theta join allows for arbitrary comparison relationships (such as ≥). An equijoin is a theta join using the equality operator. A natural join is an equijoin on attributes that have the same name in each relationship. Additionally, a natural join removes the duplicate columns involved in the equality comparison so only 1 of each … Read more
The technical definition of an identifying relationship is that a child’s foreign key is part of its primary key. CREATE TABLE AuthoredBook ( author_id INT NOT NULL, book_id INT NOT NULL, PRIMARY KEY (author_id, book_id), FOREIGN KEY (author_id) REFERENCES Authors(author_id), FOREIGN KEY (book_id) REFERENCES Books(book_id) ); See? book_id is a foreign key, but it’s also … Read more
Just promise yourself that you will never try to map a relational data model to a NoSQL database like MongoDB or CouchDB… This is the most common mistake developers make when evaluating emerging tech. That approach is analogous to taking a car and trying to use it to pull your cart down the road like … Read more
I will summarize my thoughts from my blog post – A lesson in address storage (on archive.org). On my current project [I work for a logistics company] we’re storing international addresses. I’ve done research on addresses all over the world in the design of this portion of the database. There’s a lot of different formats. … Read more