> We've got two tables set up: > > search_table > :id > :metadata1 > :metadata2 > :metadataN > :file_type > :file_size > :file_id > > file_storage > :id > :image > > everything is stored in the database. The tables could have > been combined into one, but we thought it might slow down > performance if your having to deal with a blob object in all > of your queries. The way we have it set up, we can run > queries against the lightweight search_table and then > retrieve the document from the file_id => file_storage.id > relation. Also, given this technique, the file_storage table > could easily be moved to another database, another server, or > spread out to different databases.
Actually, having the file in the same table as the other data shouldn't cause any problems. The database doesn't actually store the file in the database, it stores a pointer to a location outside of the table; the file itself is at that location. If you were storing some data that you accessed frequently, and other data that you accessed very infrequently, and all the data were stored within the tables rather than as LOBs, your table structure may actually provide some benefit. This is called a one-to-one relationship, and usually it's more trouble than it's worth. What will slow your queries down, on the other hand, is using a LOB-enabled datasource for queries that don't use LOBs. Or, at least, that used to degrade performance significantly - I haven't tested with the newest database drivers, etc. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232016 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54