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
