SQL Apprentice Question
Does it matter if we have hugh number of tables vs few tables.
One example is this.
We have a table called Vendors where VendorID is the Primary key, and
another table VendorNotes (VendorID Int, Note varchar(500)) where VendorID
is the foreign key. This is one to one relation, and all vendors won't have
Is it good practice to do like this, or just add Note column to the vendors
table, and let it be null.
And does it matter if we add many columns to a table without using it.
Please give me some advices/suggestions. I need it desperately.
>> We have a table called Vendors where VendorID is the Primary key, and another table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
ON DELETE CASCADE
ON UPDATE CASCADE,
vendor_note VARCHAR(500) NOT NULL);
The **required** uniqueness constraint has overhead. The **required**
DRI actions have overhead. Or you can take the attitude that the
database can fill up with orphans and other crap until it chokes or has
no integrity. And every time you use it, you need an OUTER JOIN. My
favorite was one of these things where a series of identifiers got
re-used and inherited orphans in the un-constrainted 1:1 table.
The cost of adding a few of NULLs is basically a bit flag to mark a
column as NULL-able, or you can default it to an empty string. That is
not looking so bad now.