Two tables in one

shakin

In light of the Omni id front page story, let me tell you about the case of the two tables in one.

When I started my current job we generated static web pages from the database and each page contained many reusable elements. In fact, each item on every web page was considered a reusable element and the HTML for these elements was kept in the database. It worked pretty well and allowed non-techie users to modify pages a great deal thanks to a crude-but-efficient page editor. The database design, however, made no sense at all.

The most important table was called Pages and the Pages.id field linked to a Relationship table that was used to solve the m:m relationship between reusable page elements and each page. However, the Relationship table was also used for defining relationships between pages themselves. If Relationship.eid was equal to any of the Elements.id values then that record in the Relationship table must be referring to the relationship between a page and an element, but if Relationship.eid was equal to a Pages.id value then that record must refer to a relationship between pages. Relationship.pid always referred to a page.

Given that both Page.id and Elements.id were bigints, the software made sure neither the Pages.id or the Elements.id conflicted by checking both tables before inserting a record in either table.

PSWorx

Same solution as in the original story: If you can't fix the broken design, add a discriminator column.

dlikhten

One of my jobs had 3 tables doing the following:

One table was: An entire pl/sql script to be compiled should the need arrise...

HOWEVER that table was inadequate since at times PL/SQL would be too long to store in a column, so we broke it up line by freaken' line in another table.

Each LINE in a pl/sql script to be created (a table containing TEXT broken up accross rows, which got compiled into a text file)... Later we use it to just refer to a single pl/sql script which we created (we didn't want to rewrite the whole damn platform).

So imagine reading that hunk of junk... And the joins were even funnier... you had to select a join of a query with "lines" (all of which are hard coded IDs, so you had to know your id) when you got that you had to sort your lines and then convert all results into text, dump it to a text file and compile to the database. YAY! Go readability!

MY GOD do some people missuse pl/sql. Especially when you have a table (yes a table) which contains the following columns

1) DB_TYPE

2) PRODUCT_NAME

3) SQL

DB_TYPE reffered to the database type for which the query applied to

PRODUCT_NAME was the product which needed the SQL

SQL was the SQL itself...

See the product was supposed to be database independent... :) Trust me whatever logical conclusions you come to, they were not used, the DB_TYPE was always Oracle, and after about 1 month SQL went from being in that table to being embedded in random parts of the code because that was easier to debug than this hunk of Sh**!