Hey Mike, That is almost exactly what I am doing. I have a table, lets say "product_fields" which describes each characteristic/field of a product (with N possible fields). A definition of a field includes, its name (as the customer see's it), its type, a named-reference to the field in the data table, and of course the id of the particular product. The "products_data" table is composed of fields sequentially labeled from f1 to fn, and contains the actual data. The only difference being that I am creating a new "products_data" table for each product (so something like "products_data_$productid"), as opposed to storing all field data in a singular large table. So the real question is, is it favorable to have one large table with N number of fields (products * fields), or N number of smaller tables (N products). I have absolutely no idea, but I will do some digging myself. Thanks for the help
Christian "Miles Thompson" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Christian, > > I did a bit of digging in the MySQL docs to try and find the limits on > number of fields in a table, etc. Didn't have much luck. I would not worry > about thousands, or even hundreds of thousands of rows in a table for two > reasons. First MySQL is becoming a heavier-duty database, and second, when > the proper indexes are used it is blindingly fast at retrievals. Apparently > the database engine also handles a large number of tables quite well. > > When tables will be multiplying like bunnies, as you posit here, I foresee > major code headaches. > > I've thought about following type of design but have never implemented it. > Might it work in your situation? Maybe this is what you are already doing, > and I've just relabelled it. If so, please accept my apologies. > > Have standard tables for the basic stuff.: company, with co_key, address > fields etc; products with prod_key, co_key, prod_name, etc. > > Have two other tables, let's call them prod_descriptors and prod_data. > We're violating one of Codd's principles here because the prod_data table > has meaningless field names, just f1, f2, f3, f4 ... fn. How you sort out > which data types to use is your business. > > Prod_descriptors contains the meta information which makes prod_data > useful. Its fields, at a minimum, would be prod_key, prod_characteristic, > prod_data_field. For a given prod_key "M56H" there would be a record for > each product characteristic which you have to track, and the field used in > prod_data. > > This may be a somewhat extreme example, as it is highly likely that there > are a common characteristics for all products, so things would not have to > be totally generalized. This design is also somewhat wasteful of disk > space, but MySQL does a remarkable job, internally, of conserving space so > it is used efficiently. The thorny issue of which data types to use in what > columns hasn't been addressed either. > > The crunch is whether or not you can ask questions of these tables and > fetch the data you need. > > > > > > At 12:52 PM 1/16/2004 +0000, Christian Calloway wrote: > >Well let me try and describe a simplified version of what I am doing. I work > >for a group of analysts, and they analyse technical products. Each products > >has about 300+ fields associated with it, and each product is vastly > >different from the other. Let's say the product "foo" has a table "fields" > >associated with it, which describes each field in foo (its name, > >description, type[float,boolean,text], order) and another table called > >"data_foo" which is 300+ fields in length. Each record in "data_foo" > >represents the technical specfiications of a single company/manufacturer. So > >if there are 200+ companies, there will be at least one data_foo record > >associated with it (in reality there is a one to many association between a > >company and the number of technical specifications they have defined).There > >are many different applications designed around the tables "foo" and > >"data_foo", and course the relationships are slightly more involved, but I > >digress. Each new product specification requires that I create a new table > >to hold that data, and with new product specifications being created every > >month, you see that the N number of tables will increase. If I store that > >data in the "fields" table or create a table that has 1-many relationship > >with it, I endup with thousands and thousands of records (Remember, each > >product has 300+ fields associated with it, and each company has at least on > >product specfication, and the N number of products continues to increase). > >Any ideas? > > > >"Miles Thompson" <[EMAIL PROTECTED]> wrote in message > >news:[EMAIL PROTECTED] > > > Christian, > > > > > > A red flag is flying. > > > > > > Usually, when people start talking about how the number of tables will > > > multiply in an application, there is a problem with the design of data > > > structures. If that's the case, now is the time to fix it. > > > > > > Miles > > > > > > At 05:35 PM 1/15/2004 +0000, Christian Calloway wrote: > > > >Hey, > > > > > > > >Sorry to post a mysql question here, but I wanted an answer from a PHP > > > >perspective. I am currently working on a database (for work) which > >contains > > > >40 tables, and will continue to expand. The reason for expansion of > >tables > > > >is to obscure to get into, but the relationships all do make sence. I > >have > > > >never worked on anything over like 50 tables, and I was wondering if I > > > >should expect a performance hit, when say, I have 100 tables, 200 tables, > > > >etc. Would it be wiser to break up the logical sections into there own > > > >databases? Wouldn't that cause a bigger hit (to performance) with > >multiple > > > >tables from multiple databases being opened on every user query? Thanks > >in > > > >advance > > > > > > > >Christian > > > > > > > >-- > > > >PHP General Mailing List (http://www.php.net/) > > > >To unsubscribe, visit: http://www.php.net/unsub.php > > > >-- > >PHP General Mailing List (http://www.php.net/) > >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
