Does many tables matters

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
notes.
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.
Thanks for any help you can provide
SteveDatabases with hundreds, even thousands, of tables is not that unusual.
Unused columns take small amounts of storage space (space is inexpensive).
The trade off is storage/retreival cost vs. development/programming cost. In
general, I think it's a 'non issue'.
Having Vendors and VendorNotes is quite acceptable -especially if the notes
are subject to frequent change and growth in size. Of course, the db purists
would say NO, all data related directly to the Vendor key *should* be in the
same table. Others would accept this arrangement for performance and
stability reasons.
So, the tried and true response is: It Depends. It depends upon what works
best for your design and the skill sets of those that have to develop and
maintian the applications that use the database.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
> 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 notes.
> 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.
> Thanks for any help you can provide
> Steve
>
>|||Thanks Arnie for your quick reply.
My assumption was that it does not take any storage space if column values
are null.
Steve,
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
> Databases with hundreds, even thousands, of tables is not that unusual.
> Unused columns take small amounts of storage space (space is inexpensive).
> The trade off is storage/retreival cost vs. development/programming cost.
> In general, I think it's a 'non issue'.
> Having Vendors and VendorNotes is quite acceptable -especially if the
> notes are subject to frequent change and growth in size. Of course, the db
> purists would say NO, all data related directly to the Vendor key *should*
> be in the same table. Others would accept this arrangement for performance
> and stability reasons.
> So, the tried and true response is: It Depends. It depends upon what works
> best for your design and the skill sets of those that have to develop and
> maintian the applications that use the database.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steve, Putman" <Steve@.noemailcom> wrote in message
> news:OY$nP5tlGHA.4144@.TK2MSFTNGP05.phx.gbl...
>|||Oh NULL values take up storage space alright.
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||NULL values do take up storage space. Not as much as, say, a CHAR(40), but
unfortunately representing NULLs does take up some space. Is space your
primary concern?
"Steve, Putman" <Steve@.noemailcom> wrote in message
news:eo2IHYwlGHA.748@.TK2MSFTNGP02.phx.gbl...
> Thanks Arnie for your quick reply.
> My assumption was that it does not take any storage space if column values
> are null.
> Steve,
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OXLSCFulGHA.4512@.TK2MSFTNGP04.phx.gbl...
>|||I tend to avoid one-to-one joins if I can. I recognize that NULLs can
take up space, and uing a 1-to-1 is a solution for that, but having a
1-to-1 join means that everytime I want to pull back information about
a vendor (including notes), I have to perform a table join. This (in
my opinion) is unnecessary in most scenarios. If an attribute of an
entity exists, then it should belong with that entity.
However, why do your vendors only have one note? This seems like a
great scenario for a 0-to-many join; if you want to add information to
a vendor, INSERT another row in your notes table. If two people are
adding notes about a vendor, then there is minimal opportunity for
concurrency issues.
In sum: if your entity (Vendor) truly has only one instance of an
attribute (VendorNote), then I would include it in the table, and allow
for NULLs (again, a design choice). However, I would first question
why is a VendorNote a singularity.
Stu
Steve, Putman wrote:
> 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 hav
e
> notes.
> Is it good practice to do like this, or just add Note column to the vendor
s
> 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.
> Thanks for any help you can provide
> Steve|||>> We have a table called Vendors where VendorID is the Primary key, and an
other table .. <<
The other table is tricky than your pseudo-code:
CREATE TABLE VendorNotes
(vendor_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Vendors(vendor_id)
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.|||Thanks Guys,
Actualy Vendors-VendorNote is just an example I gave.
Actually there are around 40 columns which we have added to a table, and
which is very very rarely used, or may never be used.
In this scenerio should be ok to keep in on a same table or better to
seperate it.
My original question was related to this.
I am still in a learning stage. So I need to follow some good practice.
Steve
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...
> The other table is tricky than your pseudo-code:
> CREATE TABLE VendorNotes
> (vendor_id INTEGER NOT NULL PRIMARY KEY
> REFERENCES Vendors(vendor_id)
> 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.
>|||When learning, it's always best to rely on theory. You can develop
"practical" work-arounds later in your career (when theory fails to
perform as well as needed in real-world scenarios). Doing a 1-to-1
join in order to build a complete entity (to avoid the storage of
NULLS) is a practical solution, not a theoretical one.
My .02
Stu
Steve, Putman wrote:
> Thanks Guys,
> Actualy Vendors-VendorNote is just an example I gave.
> Actually there are around 40 columns which we have added to a table, and
> which is very very rarely used, or may never be used.
> In this scenerio should be ok to keep in on a same table or better to
> seperate it.
> My original question was related to this.
> I am still in a learning stage. So I need to follow some good practice.
> Steve
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1151201152.819334.35550@.y41g2000cwy.googlegroups.com...