Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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 ?

share|improve this question
2  
Why not add nothing to the generic parameter table, and make the blob table more like CREATE TABLE BLOB (parameter_id number primary key, data blob, FOREIGN KEY (parameter_id) REFERENCES Parameter(id))? –  FrankieTheKneeMan Jul 12 '13 at 16:17

1 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).
share|improve this answer
    
Wondering about design more than storage. If a blob is null storage should be negligible? –  tgkprog Jul 12 '13 at 18:53
1  
As far as design is concerned, you should definitely make it a column in the table. It's just simple, good normalized design. Storage is different from rdbms to rdbms. YOu should check your documentation. –  FrankieTheKneeMan Jul 12 '13 at 19:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.