Generic params table design

Question

We have a generic parameter table whose important attributes are :

id number auto increment not null
domain varchar (200) not null
classification varchar (200) not null
param_name varchar (200) not null
value CLOB/text
created_by, created_date, updated etc

Here the id is unique for the table plus at the functional level there is a unique index on domain, classification, param_name

The value is text of any length in UTF-8. We want to add BLOB, binary data support. Choices are :

1. add a new column : blob_value BLOB OR

2. add a field 'type' of varchar2 in this table that can be null or 'B', default null. Null or blank means is not - text If B means its a blob. In that case the value will hold an id of a blob table row. The blob table will look like :

   id number auto increment data BLOB

3. Leave existing table alone and in a new table that references this one: parameter_id number primary key, data blob, with constraint FOREIGN KEY (parameter_id) REFERENCES Parameter(id) (suggestion FrankieTheKneeMan thank you)

Advantage of first is that a param can have both a text and a blob or either.

Advantage of second - not sure -> do not need to keep null columns? Current table has 30,000 rows and we expect it to grow to 70,000 out of which 10% will need blob data.

Question: which of these would you choose and why or do you have an alternate ?

Answer 1

It's a one to one relationship of Parameters to blobs - every blob must have a parameter, but not every parameter needs a blob, right? 70000 Rows doesn't seem like that much, so I'd probably just add another column to save you the join when you need blob data (joins can be deceptively expensive). Additionally, overloading the value of one column based on the value of another is a recipe for trouble. It's fine if you're the only person who's ever going to touch these tables, and you never forget anything ever. Since those are rarely the case, I'd advise against it. There's a false economy of bringing encoding into your tables.

If you're worried about storage space, try something like this on for size - add nothing to the parameter table, and instead make your primary key on your blob table also a foreign key to the param table. SOmething like:

parameter_id number primary key //ensures uniqueness
data blob
FOREIGN KEY (parameter_id) REFERENCES Parameter(id) //Ensures that it matches a parameter,
    //As well as adding a bit of speed to your select (and the cost of your inserts).