Browsing Category

Database Design

Database Management Systems

Single Instance Exception in Entity-Relationship Diagram Design

Entities in the ER (Entity Relationship) model are typically nouns, existing physically, digitally, or conceptually. It is often suggested that entities should only exist (particularly in regards to the design phase) if they will contain more than one instance. For example an Employees entity would conceivably have multiple employees. If it is known that there would only ever be one employee, and that the employee would never change, then logically any information about that employee could be kept in the Application domain, outside of the Database altogether.

The idea behind this concept is that executing queries on data which is static in nature is a resource waste.

Database Management Systems

Updating PostgreSQL fields using multiple tables

I was involved in a small project where I had to crawl through several systems to mine data, organize it, and filter it down so it could be placed into a database. The actual crawl took many days to complete and unfortunately, a small irregularity occurred during the process. Simply put, INSERTs were being used when in fact UPDATEs with a check should have been implemented.

Imagine my dismay when I found that, although the information was all in the database, it was floating around jumbled and would be difficult to re-organize, so much so that I almost feared that the a re-crawl could be necessary.

Of course, that would have been insane; after all, once you have your data in a database, there should always be some fancy, efficient way of re-organizing it.

At one point during the process, I needed to able to take data from one field and assign it to a similar field in a different table. To do so I required an UPDATE statement of a caliber I had never used (or needed) before.

At first I tried funny (and terrible) variations like:

UPDATE table_a AS a, table_b AS b SET a.this_field = a.thatfield WHERE a.some_id = b.some_id

I’m sure you’re laughing if you can see I was under the delusion that I could use an UPDATE statement like a normal SELECT statement.

Anyhow, after doing a little digging the solution is still elegant and simple simply requiring something still closely resembling an INSERT query.

I ended up using this format instead:

UPDATE private."MyData" MD SET thisid = C.thisid FROM private."ComparisonData" C WHERE MD.thatid = C.thatid AND (other statements..)

As you can see I’m using a Schema called private which you can happily ignore, and some of the tables and fields’ names have been changed to protect the innocent (or perhaps in this case, the guilty 🙂