|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Does many tables mattersDoes 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 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. -- Show quoteArnie 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, Show quote "Arnie Rowland" <ar***@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... >> 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 >> >> >> > > Oh NULL values take up storage space alright.
Show quote "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" <ar***@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... >>> 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 >>> >>> >>> >> >> > > 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? Show quote "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" <ar***@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... >>> 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 >>> >>> >>> >> >> > > 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: Show quote > 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 >> 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 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 Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1151201152.819334.35550@y41g2000cwy.googlegroups.com... >>> 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 > 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: Show quote > 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--" <jcelko***@earthlink.net> wrote in message > news:1151201152.819334.35550@y41g2000cwy.googlegroups.com... > >>> 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 > > 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. > >  |
|||||||||||||||||||||||
Other interesting topics