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 :
- add a new column : blob_value BLOB OR
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
- 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 ?
CREATE TABLE BLOB (parameter_id number primary key, data blob, FOREIGN KEY (parameter_id) REFERENCES Parameter(id))
?