Re: [PHP] db design path based
True, but I figured this php list would be a good place as well because it also comes down to programming problems that might occur. Also people who use php will be dealing with db's a lot as well. Jay is right. This list is supposed to answer only questions like my php code that queries a mysql db returned the following error... etc. -- Raditha Dissanayake. --- http://www.radinks.com/upload/ Drag and Drop Upload thousands of files and folders in a single transfer. (HTTP or FTP) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db design path based
Jay Blanchard wrote: [snip] I'm trying to build a new db for a website [/snip] Perhaps a db list would be good for this question? True, but I figured this php list would be a good place as well because it also comes down to programming problems that might occur. Also people who use php will be dealing with db's a lot as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] db design path based
[snip] I'm trying to build a new db for a website [/snip] Perhaps a db list would be good for this question? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] db design path based
Hi, I'm trying to build a new db for a website and I've got an idea on how it should work but I can't seem to figure out what the best way would be if it comes to the design of the tables in the db. Maybe I'm just looking at the the wrong way... I would like to make the website completely db driven but path based. TO get rid of the ugly uri's like index.php?pad=blaat&naar=sjips&bestand=opti I would like to use mod_rewrite so the same page can be accessed using www.domain.nl/blaat/sjips/opti. This part isn't realy a problem. The problem is how should this be implemented in the db? In the db the different levels have to be known so "sjips" is a subcategory of "blaat" and "opti" is a subcategory of "sjips" agian. In the db the name of each category and page has to be stored. This has the be easily extendable because at first there might not be a subcategory of a subcategory. Imo there shouldn't be a different column or table for each category to list it's subs, am I right? What I could be is created a table with e.g. 3 columns, one containing an unique id for each category and page, one containing each unique name of that category of page and one column containing the id of its parent. I'm not sure if this would be a good thing to do from a coding point of view because you have to contact the db multiple times to get all the information about on specific category or page, wouldn't you? It look like a simple concept imo but it might be the lack of sleep not getting me think strait anymore... Am I overlooking something? Am I creating problems which aren't there? Cheers, Age -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db design with large amount of data (?)
I'm no expert on massive amounts of data, but if you index the field which you are querying by (say you're relating the images to the product via productID), then I'd index the productID field of tblPics. Indexing (to the best of my limited knowledge) speeds up the querying process, and slows down modifications to the table... sicne you really need performance at display time, not adding/updating time, I think this will help. I know of indexes making product searches of 50,000 products, even in flat (indexed) files work really fast, so this would be an ideal first step. Justin French on 12/04/02 7:31 PM, W. Enserink ([EMAIL PROTECTED]) wrote: > Hi all, > > > i'm currently busy with db design. Since I have not worked with these > amounts of content data I have a few questions. > > > I have 2 tables: a table filled with records about products (currently 2200 > records) (tblProducts) > The next table is a table with pictures referring to product_ID's. (tblPics) > (3600 records). > Some products have more than 1 picture. I'm on apache server with mySql db. > > > Now about my db design question: > > can I better use 1 table (the tblProducts) where I store PictureNames in 1 > field, seperated by commas or whatever or should I use the tblPics as a > lookup table. > > The advantages to use tblPics as a lookup are quite clear, but each time I > want to show the pics belonging to a certain product I have to query the > lookuptable with it's 3600 records. Does this consume a lot of time?? Or > isn't 3600 records (with 8 columns) a lot. > > > > Thanx for your opinion > > Wilbert Enserink > > - > Pas de Deux > Van Mierisstraat 25 > 2526 NM Den Haag > tel 070 4450855 > fax 070 4450852 > http://www.pdd.nl > [EMAIL PROTECTED] > - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] db design with large amount of data (?)
Hi all, i'm currently busy with db design. Since I have not worked with these amounts of content data I have a few questions. I have 2 tables: a table filled with records about products (currently 2200 records) (tblProducts) The next table is a table with pictures referring to product_ID's. (tblPics) (3600 records). Some products have more than 1 picture. I'm on apache server with mySql db. Now about my db design question: can I better use 1 table (the tblProducts) where I store PictureNames in 1 field, seperated by commas or whatever or should I use the tblPics as a lookup table. The advantages to use tblPics as a lookup are quite clear, but each time I want to show the pics belonging to a certain product I have to query the lookuptable with it's 3600 records. Does this consume a lot of time?? Or isn't 3600 records (with 8 columns) a lot. Thanx for your opinion Wilbert Enserink - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] db design
I would make 2 tables, a product table and a language table... It would look like so: Product ID | product name | manufacturer | etc Then a language table that looks like: Language ID | Product ID | product description | etc Then you won't be replicated your 11 columns since they are not in the product table anymore.. If there are 100 products, and 1 language then there will be 100 records in the product table and 100 in the language table. If there are 2 languages then there will be 200 in the language table. This method means you will be able to expand your language endless... and I don't think your DB Server will be slowed down too much by this, since u can pull this all of with 1 query.. something like (from memory) SELECT tblProducts.name, tblLanguage.Description FROM tblProducts, tblLanguage WHERE tblProducts.productID=tblLanguage.productID AND {some other query} Hope this helps Andrew - Original Message - From: "Wilbert Enserink" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, February 15, 2002 12:33 PM Subject: [PHP] db design > hi all, > > > I need some tips on database (mySQL) design. > The problems lie in languages. I'm gonna make a e-commerce webiste. it's > rather big (in my terms/experience). The website should be expandible easily > with regard to languages/translations. > > My biggest table with product descriptions has 43 columns. Other tables > include a list of FAQ's, manufacturers and so on. > > What do you think I have to do? Make a new table for each language, or > define more columns for each language within a table? What is normal in > this, also with regard to doing queries? > > e.g. In the table with 43 columns, there are 11 columns which are the same > for each language (product name, manufacturer and so on) The other columns > will depend on language (like product description). > --If I use a seperate table for each language than there is double info (no > normalization with regard to the 11 columns) > --If I use more columns, than I have 32 columns extra per language. If I > make 10 translations, then this table will reach over 10x32=320 columns. > > I need somebody who can give me overall info on this, or shine a broad light > on my Q. Does the db design has a large influence on flexibility with regard > to future expanding? And how about query times (response) on a server. I > don't want it to get too slow > > Well I think, you know my point now, > > any info is much appreciated!! > > > thx in advance, > > Wilbert Enserink > > > - > Pas de Deux > Van Mierisstraat 25 > 2526 NM Den Haag > tel 070 4450855 > fax 070 4450852 > http://www.pdd.nl > [EMAIL PROTECTED] > - > > -- > 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] db design
hi all, I need some tips on database (mySQL) design. The problems lie in languages. I'm gonna make a e-commerce webiste. it's rather big (in my terms/experience). The website should be expandible easily with regard to languages/translations. My biggest table with product descriptions has 43 columns. Other tables include a list of FAQ's, manufacturers and so on. What do you think I have to do? Make a new table for each language, or define more columns for each language within a table? What is normal in this, also with regard to doing queries? e.g. In the table with 43 columns, there are 11 columns which are the same for each language (product name, manufacturer and so on) The other columns will depend on language (like product description). --If I use a seperate table for each language than there is double info (no normalization with regard to the 11 columns) --If I use more columns, than I have 32 columns extra per language. If I make 10 translations, then this table will reach over 10x32=320 columns. I need somebody who can give me overall info on this, or shine a broad light on my Q. Does the db design has a large influence on flexibility with regard to future expanding? And how about query times (response) on a server. I don't want it to get too slow Well I think, you know my point now, any info is much appreciated!! thx in advance, Wilbert Enserink - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] RE: [PHP-DB] Design conundrum...
> The problem involves 2 tables, one of editors and one of categories, both > have unique ID numbers. Each editor can have authority over an arbitrary > number of categories, and conversely, each category can have an arbitrary > number of editors. So what is the best way to represent this in the DB? > (Which is PostgreSQL 7 BTW) This is a so-called N:N relation, and this usually calls for a third table to store the cross-relations: table editors id, name, email table categories id, name table ediors_categories editor (id of editor) category (id of category) Now if you want to give an editor authority over a category, you would insert a new record into editors_categories with the respective ids of the editor and the category. If you want to select all categories that belong to a certain author, you would do this: SELECT c.* FROM categories c LEFT JOIN editors_categories ed ON c.id = ed.category WHERE ed.editor = '" . $editor_id . "'" This selects all category records and joins them with the editors_categories table, then filtering the resulting recordset by the editor id stored in the editors_categories table. Hope this helps Kristian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]