A piece of ... Art

Jeremy Tierman

Some DB geniuses created this and more.

CREATE TABLE [dbo].[ImportDownload_85_Work](
[ImportFileID] [int] NOT NULL,
[RowID] [int] NOT NULL,
[Data001] [varchar](800) NULL,
[Data002] [varchar](800) NULL,
[Data003] [varchar](800) NULL,
[Data004] [varchar](800) NULL,
[Data005] [varchar](800) NULL,
[Data006] [varchar](800) NULL,
[Data007] [varchar](8000) NULL,
[Data008] [varchar](8000) NULL,
[Data009] [varchar](8000) NULL,
[Data010] [varchar](8000) NULL,
[Data011] [varchar](8000) NULL,
...
[Data195] [varchar](8000) NULL,
[Data196] [varchar](8000) NULL,
[Data197] [varchar](8000) NULL,
[Data198] [varchar](8000) NULL,
[Data199] [varchar](8000) NULL
) ON [PRIMARY]

There are over 100 tables like this on one filegroup and one file! X| Oh yea, the identical copies for each suffixed _Last instead of _Work :confused:

Luc Pattyn

Great. He plans on storing data in there!? :omg:

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

PIEBALDconsult

Looks like a temporary holding place some sort of imported data to me.

Luc Pattyn

it is a database after all... :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

Jeremy Tierman

Unfortunately, it is a permanent import repository.

killabyte

code looks great, it is obviously a temp data pool from SETI signal recievers ready to be split up and sent out to all those who have the SETI screen saver, processed then sent back, injected into the real tables that have a naming convention, it is only obfuscated here to protect sensitive national security secrets. ;P

Brady Kelly

Generics comes to SQL. :)

CARPETBURNER

SELECT NULL FROM Brain Where Engaged = 0

Keith Barrow

What's not to love... :-D

Jeroen De Dauw

LOL! I think he omitted that table :)

GSoC 2009 student for SMW! --- My little forums: http://code.bn2vs.com --- 70 72 6F 67 72 61 6D 6D 69 6E 67 20 34 20 6C 69 66 65!

costas0811

Which Normal Form is that?

ssiegel

Must be government work!

Sam

Jeremy Tierman

First Imaginary Form :laugh:

Kelly Herald

That looks a lot like the Oracle tables at the company that I used to work at. I really hated having to query that database. You didn't have any clue as to what each field was being used for. X|

Kelly Herald Software Developer

puromtec1

This table design could be used to reduce the size of a database and improve manageability that contains unknown/highly dynamic structures I will make an assumption (the only one in this post) and say this project deals with loading structures that change frequently. Could be wrong and be looking at a noob's 'work of art' There are three approaches for highly dynamic structural data: 1. Dynamically create the schema's based on external blue-prints, giving real names to columns. This might not be the best solution for a few subtle reasons which have to do with the degree of knowledge by the end-user's of the database and any planned api's or DAL's. Generating the dynamic schema prior to loading data can be done in two modes: 1. immediately, where new un-generated schemas are encountered and have to be generated on the spot. This presents an obvious problem if you have multiple clients performing the imports for a single database (similar to SETI). Such as, what if two clients encounter the same type of schema that isn't in the database yet, only one schema should be generated. 2. Delayed, where after loading many imports and discovering that some new files failed to match any current dynamic schema, the database user can manually kick off schema generation and then attempt a reload of those files that failed earlier. 2. Contain the all the data into a single table (or a table for each datatype) with four columns (ImportFileId, RowId[original structure's], ParameterName, ParameterValue). This solution gobbles up WAY MORE disk space (more than doubles the space requirement of the solution given above as well as in #1 (In oracle, at least, the remaining columns that are null take up no space.) This is considered serializing the data, also could be called demuxing. 3. Then there is the solution above. The only requirement is that any API must map the ImportFileId to some blue-print of the columns involved. This solution saves much more space than #2 AND has the added benefit of simplicity on the database up-keep side (ie. no schema generation prior to import) I've been down this road dealing with a database with over 400,000 actual unique fields of data. We actually used option #1 for good reason. So, I wouldn't knock it just because it's different.