Re: [GENERAL] table count limitation
Be aware of one thing: having a seperate table for each product type makes it extremely difficult to scale your application. In your plan, everytime you add a new product type you have to create a new table. Which means if you have any sort of general "search all product types for this attribute" type search, you have to rewrite the sql for that search every time, etc. Of course with postgres you can get around some of this by making a root_object type table with attributes that all products share and then have your individual tables inherit from it so you can at least user postgres's inheritance to help your searches. I still think that finding some way to do this with fewer tables is your best bet, but I don't know the specifics of your situation. - Water overcomes the stone; Without substance it requires no opening; This is the benefit of taking no action. Lao-Tse Brian Knox Senior Systems Engineer [EMAIL PROTECTED] On Sun, 27 Aug 2000, Marcin Inkielman wrote: > On Sun, 27 Aug 2000, Miles Thompson wrote: > > > > Each table you would create for a different type of product can be replaced > > with a single column, say "product_type", which you could use to broadly > > classify your items: cars, boats, computers, appliances, etc. > > don't you think that this is exectly the 2nd column in my table I > previousely defined? > > > CREATE TABLE products( > > product_id int4, > > product_type_id int4, > > feature_id int4, > > value text/float/... > > ) > > > Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as > > many as you need, for the different attributes of each item. So "descrip1" may > > contain what you may describe as the brand, or the make, or the model, or the > > processor type of an item in your user interface. > > that's a possibility, however remember that I don't know right now how > many descriptors I will have for each product type, and how many types I > will have. Also, if I use a general form for a descriptor I will have to > chose a text field and it is not very effective to examine that type of > fields if they contains numbers, dates, etc... Indexes put on the > descriptor columns wont be very effective as one column will describe > different products features - dont you think? > > > The difficult questions to answer are: How many types of products? > > That's exactly I don't know! I hope to to exceed 1 ;) > > > How many > > meaningful attributes for each? Base your table design on the answers, and I'd > > bet you start to run out at about 6 or so. How fine grained do you want to > > make your search? > > I agreed. 6 up to 10 should suffice. > > > You could take this further, and have a "product_type" table. For each major > > item, you would have a standard set of attributes you could use to populate > > pick lists, so that users would not ask for porcelain sailboats with PIII > > processors of the Ming dynasty. These same lists would be used in the > > maintenace forms you use to add items, so you have a consistency. > > in fact, the problem I presented is only a part of bigger one... I have to > construct a full system of product classification. Of course I will have > to construct a table of product types and a table of product type > descriptors. > > > This table could also contain the labels, the applicable descriptions for each > > product_type. When a user selected a particular product you could fetch the > > descriptive text for each field and load it into your interface as the > > descriptor for the field. > > that is exactly my system is doing now... ;) > > > Contact managers do this all the time - the "user > > definable" fields have standard names and the user just supplies the label. > > > > This saves a lot of work - one or two standard forms displayed with "custom" > > labels, standard queries which run when you click on "Submit", and standard > > results forms. (I'm calling them forms, they could be web pages.) Even > > background colours and logos could change according to product type. > > > > > If i could have 1 tables - one table for each type of products this > > > queries would be a lot simplier - don't you think? > > My question sent to the mailing list was if it is possible to work with > postgres+a lot of tables. In fact I am planning to use this tables only > for selects purpose just like multi-cross tables in M$ Access and > internally to use a table of product types, a table of descriptors of > product types (feature vectors) and table of products as defined above. > > > You're doing great. > Thx ;)) > > I had a siimlar discussion a short while ago with someone > > who wanted to do classified ads for agricultural products: livestock, feed, > > tractors, etc. Thinking out a good design for your database is hard work. > > I am not sure if SQL is the best language to use with that types of > problems, I use it because SQL-dbases are the
Re: [GENERAL] table count limitation
Marcin .. Marcin Inkielman wrote: > On Sun, 27 Aug 2000, Miles Thompson wrote: > > > > Each table you would create for a different type of product can be replaced > > with a single column, say "product_type", which you could use to broadly > > classify your items: cars, boats, computers, appliances, etc. > > don't you think that this is exectly the 2nd column in my table I > previousely defined? > > > CREATE TABLE products( > > product_id int4, > > product_type_id int4, > > feature_id int4, > > value text/float/... > > ) Yes - apologies! > > > > Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as > > many as you need, for the different attributes of each item. So "descrip1" may > > contain what you may describe as the brand, or the make, or the model, or the > > processor type of an item in your user interface. > > that's a possibility, however remember that I don't know right now how > many descriptors I will have for each product type, and how many types I > will have. Also, if I use a general form for a descriptor I will have to > chose a text field and it is not very effective to examine that type of > fields if they contains numbers, dates, etc... Indexes put on the > descriptor columns wont be very effective as one column will describe > different products features - dont you think? My thinking there was that you would use whatever datatype was appropriate, and I'd guess that most of them would be character fields, except for date information. So descrip1 could be used for "Make" for cars, appliances, camera's, lenses, etc.; or for "Fabric" for clothing, linens, antique garments etc. Wether you want to use numbers to encode this information, or descriptive text, is up to you. > > The difficult questions to answer are: How many types of products? > > That's exactly I don't know! I hope to to exceed 1 ;) That's why I think a single table, with appropriate fields will be more powerful and flexible. > > How many > > meaningful attributes for each? Base your table design on the answers, and I'd > > bet you start to run out at about 6 or so. How fine grained do you want to > > make your search? > > I agreed. 6 up to 10 should suffice. > > > You could take this further, and have a "product_type" table. For each major > > item, you would have a standard set of attributes you could use to populate > > pick lists, so that users would not ask for porcelain sailboats with PIII > > processors of the Ming dynasty. These same lists would be used in the > > maintenace forms you use to add items, so you have a consistency. > > in fact, the problem I presented is only a part of bigger one... I have to > construct a full system of product classification. Of course I will have > to construct a table of product types and a table of product type > descriptors. > > > This table could also contain the labels, the applicable descriptions for each > > product_type. When a user selected a particular product you could fetch the > > descriptive text for each field and load it into your interface as the > > descriptor for the field. > > that is exactly my system is doing now... ;) Good, forward thinking -- a little pain now for a lot of gain later on. > > Contact managers do this all the time - the "user > > definable" fields have standard names and the user just supplies the label. > > > > This saves a lot of work - one or two standard forms displayed with "custom" > > labels, standard queries which run when you click on "Submit", and standard > > results forms. (I'm calling them forms, they could be web pages.) Even > > background colours and logos could change according to product type. > > > > > If i could have 1 tables - one table for each type of products this > > > queries would be a lot simplier - don't you think? > > My question sent to the mailing list was if it is possible to work with > postgres+a lot of tables. In fact I am planning to use this tables only > for selects purpose just like multi-cross tables in M$ Access and > internally to use a table of product types, a table of descriptors of > product types (feature vectors) and table of products as defined above. > > > You're doing great. > Thx ;)) > > I had a siimlar discussion a short while ago with someone > > who wanted to do classified ads for agricultural products: livestock, feed, > > tractors, etc. Thinking out a good design for your database is hard work. > > I am not sure if SQL is the best language to use with that types of > problems, I use it because SQL-dbases are the most developped and > reliable, however I think that a hierarchical db should be more > appropriate. That's really the structure we're imposing here, isn't it. > > PS You are in Poland? What part? My wife's grandparents emigrated from around > > Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt > > I live near Warsaw. LÓd¼ (Lodz - if your e-mail reader do not support > iso-8859-2 ;) ) is ~100km from here. You must be
Re: [GENERAL] table count limitation
Andrew, As I was reading Marcin's reply to my message of earlier today I began to think of how PGSQL has been extended. My thinking was purely relational, an "objectified" approach would probably be worth considering. [EMAIL PROTECTED] wrote: > On Sun, 27 Aug 2000, Miles Thompson wrote: > > > Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as > > many as you need, for the different attributes of each item. So "descrip1" may > > Now someone once said to me "If you find your labeling your fields x1, x2, > x3... then your design is wrong." His next step would be to ask for your > ER diagram :-) Yeah! I used those because they were general - attributes for outboard motors would be quite different than those for antique glass or sports cars. > Have you tried inheritance? i.e you will have a product, inheriting from > that you will have computers, boats, cars etc. Perhaps from boats you > could have sail boats, motor boats etc as you store different attributes. > > As postgresql is an OORBMS it should be able to handle it without any > nasty squash into the relational model tricks. Agreed, but won't you then have a mess of tables, different forms, different reports to contend with? What's the difference between having this and a product type field? I could be wrong, but let's say we take Marcin's earlier table description, and call it item. Then we subclass it to boats ... boats = extends item and then we subclass boats ... sailboats = extends boats motorboats = extends boats runabouts= extends motorboats trawlers = extends motorboats I think we end up with a hell of a mess, and the parent class is 3 levels up for trawlers and runabouts. I've not worked with object-relational or object-oriented databases, so I could be missing something, and I'd be willing to be proven wrong. We're also back to Marcin's original question - Is there a limit on the number of tables? I don't know, but I'm very uncomfortable with a new set of tables created for every variation in a product or item type. > I've never used an OO database in production mind you...perhaps there is > some catch I'm not aware of. I'd love to hear from someone who has done one. Regards - Miles Thompson
Re: [GENERAL] table count limitation
On Sun, 27 Aug 2000, Miles Thompson wrote: > Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as > many as you need, for the different attributes of each item. So "descrip1" may Now someone once said to me "If you find your labeling your fields x1, x2, x3... then your design is wrong." His next step would be to ask for your ER diagram :-) Have you tried inheritance? i.e you will have a product, inheriting from that you will have computers, boats, cars etc. Perhaps from boats you could have sail boats, motor boats etc as you store different attributes. As postgresql is an OORBMS it should be able to handle it without any nasty squash into the relational model tricks. I've never used an OO database in production mind you...perhaps there is some catch I'm not aware of. Andrew
Re: [GENERAL] table count limitation
On Sun, 27 Aug 2000, Miles Thompson wrote: > > Each table you would create for a different type of product can be replaced > with a single column, say "product_type", which you could use to broadly > classify your items: cars, boats, computers, appliances, etc. don't you think that this is exectly the 2nd column in my table I previousely defined? > CREATE TABLE products( > product_id int4, > product_type_id int4, > feature_id int4, > value text/float/... > ) > Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as > many as you need, for the different attributes of each item. So "descrip1" may > contain what you may describe as the brand, or the make, or the model, or the > processor type of an item in your user interface. that's a possibility, however remember that I don't know right now how many descriptors I will have for each product type, and how many types I will have. Also, if I use a general form for a descriptor I will have to chose a text field and it is not very effective to examine that type of fields if they contains numbers, dates, etc... Indexes put on the descriptor columns wont be very effective as one column will describe different products features - dont you think? > The difficult questions to answer are: How many types of products? That's exactly I don't know! I hope to to exceed 1 ;) > How many > meaningful attributes for each? Base your table design on the answers, and I'd > bet you start to run out at about 6 or so. How fine grained do you want to > make your search? I agreed. 6 up to 10 should suffice. > You could take this further, and have a "product_type" table. For each major > item, you would have a standard set of attributes you could use to populate > pick lists, so that users would not ask for porcelain sailboats with PIII > processors of the Ming dynasty. These same lists would be used in the > maintenace forms you use to add items, so you have a consistency. in fact, the problem I presented is only a part of bigger one... I have to construct a full system of product classification. Of course I will have to construct a table of product types and a table of product type descriptors. > This table could also contain the labels, the applicable descriptions for each > product_type. When a user selected a particular product you could fetch the > descriptive text for each field and load it into your interface as the > descriptor for the field. that is exactly my system is doing now... ;) > Contact managers do this all the time - the "user > definable" fields have standard names and the user just supplies the label. > > This saves a lot of work - one or two standard forms displayed with "custom" > labels, standard queries which run when you click on "Submit", and standard > results forms. (I'm calling them forms, they could be web pages.) Even > background colours and logos could change according to product type. > > > If i could have 1 tables - one table for each type of products this > > queries would be a lot simplier - don't you think? My question sent to the mailing list was if it is possible to work with postgres+a lot of tables. In fact I am planning to use this tables only for selects purpose just like multi-cross tables in M$ Access and internally to use a table of product types, a table of descriptors of product types (feature vectors) and table of products as defined above. > You're doing great. Thx ;)) > I had a siimlar discussion a short while ago with someone > who wanted to do classified ads for agricultural products: livestock, feed, > tractors, etc. Thinking out a good design for your database is hard work. I am not sure if SQL is the best language to use with that types of problems, I use it because SQL-dbases are the most developped and reliable, however I think that a hierarchical db should be more appropriate. > PS You are in Poland? What part? My wife's grandparents emigrated from around > Lodz in 1904~06 and settled in Winnipeg, Manitoba in Western Canada. /mt I live near Warsaw. LÓdź (Lodz - if your e-mail reader do not support iso-8859-2 ;) ) is ~100km from here. You must be a very happy man if your wife is partially from Poland. Polish women are really beautifull! ;)) regards, Marcin Inkielman
Re: [GENERAL] table count limitation
Marcin ... comments below Marcin Inkielman wrote: > On Sun, 27 Aug 2000, Jurgen Defurne wrote: > > > Any time your design is heading in this direction, take a good hard look > at > > it. Proper organization with the appropriate indexes is the way to go. > > > > With tens of hundreds of tables, how will you decide which to use? > > How will you write your queries? Customize them for the different > > tables? > > Will you be generating a lot of data, thereby creating a lot of tables? > > How > > long will they take to create and populate? > > > > With fewer, large tables you are appending data at the end, and > > maintaining > > indexes. An inherently simpler operation. Queries are written to a known > > > > design and structure. You will, admittedly, have large index files, but > > you > > will not have hundreds to thousands of tables, each with indexes. > > > > The Fishcart ecommerce system, which can be implemented in PostgreSQL, > > has > > only 20 tables, four of which have any degree of traffic. > > > > A proprietary system done in here in Halifax for the employer's > > association has about 16 core tables, two of them are regularly updated, > the rest > > contain relatively static information on members, rates, tax rates, > piers, etc. > > > > Rethink your design, talk it over with the fencepost, draw little > > pictures, ask "what if", do some rough data storage calculations -- but > the > > general rule of thumb, with proper normalization, is "fewer is better". > > > > Regards - Miles Thompson > > Thank you for the comments! > > I think You are right in most situations, however do you think that "fewer > is better" in situation like this: > > I have to construct a catalogue of several types of products (1 > types). Each type is defined by a different set of features. > > If I do it like You suggest I have to create a table: > > CREATE TABLE products( > product_id int4, > product_type_id int4, > feature_id int4, > value text/float/... > ) > > of course, it is relatively simple to describe any number of products > having different types in this table. > > however... how may I select a set of product having the same type using > this table EFFECTIVELY. For example:w > I have to select: > - all PC with PII/450Mhz and 128MB > or > - all red Renault Megane / 1600GL > > Note that each product is described by several rows in the table (each > type of products is characterised by other number of features) and I dont > have to compare (select) products having other types (i.e. cars and > computers in 1 querry). Each table you would create for a different type of product can be replaced with a single column, say "product_type", which you could use to broadly classify your items: cars, boats, computers, appliances, etc. Other columns could simply be labelled "descrip1", "descrip2", "descrip3", as many as you need, for the different attributes of each item. So "descrip1" may contain what you may describe as the brand, or the make, or the model, or the processor type of an item in your user interface. The difficult questions to answer are: How many types of products? How many meaningful attributes for each? Base your table design on the answers, and I'd bet you start to run out at about 6 or so. How fine grained do you want to make your search? For a car this may mean: make, model, model type, colour, year, price For a boat: power/sail/other - and then you get into questions which are particular for each, and can't be answered by a "one kind suits all" form. For sail you would have questions of rig, size, construction material, age; for power length, seats, inboard/outboard/ etc. Classifications are difficult, as people tend to think of similar things in different ways. Don't rule out the possibility of a text field which can be searched once a primary selection is made. To continue with the car example, once a set of 20 or has been returned, your user may then search for "leather upholstery", but then at least only 20 text fields are being scanned, not 20,000. If that expression isn't in the general field, well that's unfortunate. You could take this further, and have a "product_type" table. For each major item, you would have a standard set of attributes you could use to populate pick lists, so that users would not ask for porcelain sailboats with PIII processors of the Ming dynasty. These same lists would be used in the maintenace forms you use to add items, so you have a consistency. This table could also contain the labels, the applicable descriptions for each product_type. When a user selected a particular product you could fetch the descriptive text for each field and load it into your interface as the descriptor for the field. Contact managers do this all the time - the "user definable" fields have standard names and the user just supplies the label. This saves a lot of work - one or two standard forms displayed with "custom" labels, standard queries which run when you click on "
Re: [GENERAL] table count limitation
On Sun, 27 Aug 2000, Jurgen Defurne wrote: > Any time your design is heading in this direction, take a good hard look > at > it. Proper organization with the appropriate indexes is the way to go. > > With tens of hundreds of tables, how will you decide which to use? > How will you write your queries? Customize them for the different > tables? > Will you be generating a lot of data, thereby creating a lot of tables? > How > long will they take to create and populate? > > With fewer, large tables you are appending data at the end, and > maintaining > indexes. An inherently simpler operation. Queries are written to a known > > design and structure. You will, admittedly, have large index files, but > you > will not have hundreds to thousands of tables, each with indexes. > > The Fishcart ecommerce system, which can be implemented in PostgreSQL, > has > only 20 tables, four of which have any degree of traffic. > > A proprietary system done in here in Halifax for the employer's > association > has about 16 core tables, two of them are regularly updated, the rest > contain > relatively static information on members, rates, tax rates, piers, etc. > > Rethink your design, talk it over with the fencepost, draw little > pictures, > ask "what if", do some rough data storage calculations -- but the > general rule > of thumb, with proper normalization, is "fewer is better". > > Regards - Miles Thompson Thank you for the comments! I think You are right in most situations, however do you think that "fewer is better" in situation like this: I have to construct a catalogue of several types of products (1 types). Each type is defined by a different set of features. If I do it like You suggest I have to create a table: CREATE TABLE products( product_id int4, product_type_id int4, feature_id int4, value text/float/... ) of course, it is relatively simple to describe any number of products having different types in this table. however... how may I select a set of product having the same type using this table EFFECTIVELY. For example:w I have to select: - all PC with PII/450Mhz and 128MB or - all red Renault Megane / 1600GL Note that each product is described by several rows in the table (each type of products is characterised by other number of features) and I dont have to compare (select) products having other types (i.e. cars and computers in 1 querry). If i could have 1 tables - one table for each type of products this queries would be a lot simplier - don't you think? PS. sorry for my English - I hope I was understood -- Marcin Inkielman
Re: [GENERAL] table count limitation
Marcin Inkielman wrote: > On Sat, 26 Aug 2000, Jurgen Defurne wrote: > > > Date: Sat, 26 Aug 2000 07:36:25 +0200 > > From: Jurgen Defurne <[EMAIL PROTECTED]> > > To: Marcin Inkielman <[EMAIL PROTECTED]> > > Cc: postgreSQL general mailing list <[EMAIL PROTECTED]> > > Subject: Re: [GENERAL] table count limitation > > > > Marcin Inkielman wrote: > > > > > HI! > > > > > > I have such problem: > > > Is the amount of tables limited in Postgresql7.0? > > > Has anybody tried to use EFFECTIVELY a database > > > with 1 tables at all? > > > > > > Thx for help. > > > > Are you really sure you NEED a database with 1 tables ? > > yes ;) I suggest you read this message which also came up today on the mailing list : Patrick, Any time your design is heading in this direction, take a good hard look at it. Proper organization with the appropriate indexes is the way to go. With tens of hundreds of tables, how will you decide which to use? How will you write your queries? Customize them for the different tables? Will you be generating a lot of data, thereby creating a lot of tables? How long will they take to create and populate? With fewer, large tables you are appending data at the end, and maintaining indexes. An inherently simpler operation. Queries are written to a known design and structure. You will, admittedly, have large index files, but you will not have hundreds to thousands of tables, each with indexes. The Fishcart ecommerce system, which can be implemented in PostgreSQL, has only 20 tables, four of which have any degree of traffic. A proprietary system done in here in Halifax for the employer's association has about 16 core tables, two of them are regularly updated, the rest contain relatively static information on members, rates, tax rates, piers, etc. Rethink your design, talk it over with the fencepost, draw little pictures, ask "what if", do some rough data storage calculations -- but the general rule of thumb, with proper normalization, is "fewer is better". Regards - Miles Thompson Patrick Goodwill wrote: > Hi! > > I'm writing a system which i could logically separate it into hundreds, > perhaps thousands, of tables, or it could put it all into one big table. > Since each tables could probably only grow to 10s of MBs in size, from a > design, speed, and scalability perspective, is it perferable to split up > the tables (as I've currently programmed it) or to put everything into one > gigantic, multi-GB table? This is for the web, so transaction speed is > important. > > Patrick. Jurgen
Re: [GENERAL] table count limitation
Marcin Inkielman wrote: > HI! > > I have such problem: > Is the amount of tables limited in Postgresql7.0? > Has anybody tried to use EFFECTIVELY a database > with 1 tables at all? > > Thx for help. Are you really sure you NEED a database with 1 tables ? Jurgen
