Anchor modeling is a nice way to implement a temporal dB — see the Wikipedia article too. Takes some time to get used to, but work nice. There is an online modeling tool and if you load the supplied XML file [File -> Load Model from Local File] you should see something like this — … Read more
The answer depends a little if you are limited to freeware such as PostGreSQL (not fully SQL compliant), or if you are thinking about SQL (ie. SQL compliant) and large databases. In SQL compliant, Open Architecture databases, where there are many apps using one database, and many users using different report tools (not just the … Read more
I actually use this as one of my interview questions. The following is a good place to start: Addresses ——— AddressId (PK) Street1 … (etc) and AddressTypes ———— AddressTypeId AddressTypeName and UserAddresses (substitute “Company”, “Account”, whatever for Users) ————- UserId AddressTypeId AddressId This way, your addresses are totally unaware of how they are being used, … Read more
There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than … Read more
You have this python code: c.execute(“insert into medicoes values(?,?,?,?,?,?,?)” % keys) which I think should be c.execute(“insert into medicoes values (?,?,?,?,?,?,?)”, keys) since the % operator expects the string to its left to contain formatting codes. Now all you need to make this work is for keys to be a tuple (or list) containing the … Read more
This is not possible with a simple DEFAULT value, as the manual clearly states: The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). You could use a trigger instead: CREATE OR REPLACE FUNCTION trg_foo_b_default() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN — For just a … Read more
A. Preliminary Your Approach First and foremost, I must commend your attitude. It is rare to find someone who not only thinks and works from a solid grounding, and who wishes to understand and implement a Double-Entry Accounting system, instead of: either not implementing DEA, thus suffering multiple re-writes, and pain at each increment, each … Read more
I would lean towards a deleted_at column that contains the datetime of when the deletion took place. Then you get a little bit of free metadata about the deletion. For your SELECT just get rows WHERE deleted_at IS NULL
Mythology I always thought that databases should be denormalized for reading, as it is done for OLAP database design, and not exaggerated much further 3NF for OLTP design. There’s a myth to that effect. In the Relational Database context, I have re-implemented six very large so-called “de-normalised” “databases”; and executed over eighty assignments correcting problems … Read more
A sparse column doesn’t use 4x the amount of space to store a value, it uses a (fixed) 4 extra bytes per non-null value. (As you’ve already stated, a NULL takes 0 space.) So a non-null value stored in a bit column would be 1 bit + 4 bytes = 4.125 bytes. But if 99% … Read more