Hi, I''m working on the db schema for my rails app, and i''ve got a bunch of small tables with only 1 or 2 fields in them. For example, here''s a couple of them: product_names ------------- id p_name vendor_names ------------ id v_name classifications ------------ id classification_type What I''d like to do is to somehow make a ''virtual'' table called ''products'' that incorporates all the above table fields without having to actually create the table in the db. The reason I''m thinking this is because I eventually want a form in my app which includes a dropdown box to choose a product name. However, it is possible that I could have data such as: 1 Product1 Vendor1 Class1 2 Product1 Vendor2 Class1 3 Product2 Vendor1 Class1 4 Product2 Vendor2 Class2 So it is possible to have duplicate product names in the table but each data row would be unique. A side effect of this is that populating a dropdown with this data leads to duplicate entries in the list, and the user wouldnt know which vendor/class combination they were selecting along with the product name. I realize I could create a table like: products ------- id product_name_id vendor_name_id classification_id in the DB but I get the feeling there is another way to do it, but I''m not sure what. I dont know, maybe that is the way to go, I''d like to get some feedback from you all. If I have a lot of small tables such as these in my DB, am I doing something wrong? Is this a performance bottleneck? Do I need to go back and read RDBMS Design 101? Any insight, as usual, would be most appreciated. Thanks!
Patrick J. Franz -- ML
2005-May-31 03:43 UTC
Re: Several small DB tables into one big table
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Lester-- Lester wrote:> Hi, > > I''m working on the db schema for my rails app, and i''ve got a bunch of > small tables with only 1 or 2 fields in them. For example, here''s a couple > of them: > > product_names > ------------- > id > p_name > > vendor_names > ------------ > id > v_name > > classifications > ------------ > id > classification_type >I''m far from an expert in DB design, but my current thinking would lead me to ask about the ''classifications'': are they intrinsic to either the product or the vendor such that either of those tables could pick up a classification_id? Let''s say, hypothetically, that the classification has something to do with the product_names (and I''m going to call that table products from here on out). You could create a table products - -------- id name classification_id Then you could have a table: products_vendors - ---------------- product_id vendor_id and your classifications table: classifications - --------------- id type You can then add the two models: class Product < ActiveRecord::Base belongs_to :classification has_and_belongs_to_many :vendors end class Vendor < ActiveRecord::Base has_and_belongs_to_many :products end Then you can write Ruby code in your controllers that gives you the data you were referring to: products = Vendor.find(some_vendor_id).products You could access the product classifications but iterating over the products: products.each { |p| p.classification.type } This can also be used to get the vendors for a given product: vendors = Product.find(some_product_id).vendors I hope that this helps you and, like I said, I''m still learning some of this stuff myself, so you should probably wait to see if someone refutes me or not ;) Patrick - -- ~~~~~~~~~~ Patrick J. Franz http://assert23.z-dyne.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCm91GnBL/CxPCaScRAgK8AKCltnJV/vn/4JJzuZNl65zlO1ROTwCeIRCL pOTQoiomNSzS+yPiHSZy4pI=ATyq -----END PGP SIGNATURE-----