Oh yes... sometimes you will want to use an extension table...
My convention names them the same with _ext on the end of the table name, and _ext_id for the id rather than _id...
You usually use extension tables for large data fields that are not used very often... They are 1:1, so you should never have two with the same id... which is why the id for the extension table is the same as the id for the table it is extending.
example
--------------------
ex_id integer
ex_text varchar(255)
ex_number integer
example_ext
--------------------
ex_ext_id integer
ex_massivetext text
ex_blob blob
ex_clob clob
ex_othermassivejunk blob
So, now when you retrieve a record from example, you don't retrieve all the massive stuff that is in the ext table... plus, it is the safest way to update a table! Add a field to the extension table and make sure you always use SQL statements that specify the fields when using the ext table, and you can use the extension table more freely, since you know it will never return a large record, and the structure will not be changing.
Just another $0.01 from the mess that is e-dawg's mind.
[This message has been edited by e_dawg (edited December 31, 2001).]
