Re: how to prevent from Creating Large Numbers of Tables ???
On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Hello Xuefer, You can use one or more SET type columns to store the properties. See http://www.mysql.com/doc/S/E/SET.html for more details. Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: how to prevent from Creating Large Numbers of Tables ???
SET? i know how to use SET but... attribute of products is value such as Size Weight Color and so on the problem is, different CATE of products have different bunch of attribute On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Hello Xuefer, You can use one or more SET type columns to store the properties. See http://www.mysql.com/doc/S/E/SET.html for more details. Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: how to prevent from Creating Large Numbers of Tables ???
On Tue, 2002-06-18 at 12:28, Xuefer wrote: SET? i know how to use SET but... attribute of products is value such as Size Weight Color and so on the problem is, different CATE of products have different bunch of attribute Heh. Sorry - should have read more carefully. On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Option one is good if you have a small number of attributes that are not likely to change. A major drawback is that you will need to alter the table to add/delete additional properties. Option two is more flexible, and will be more efficient for large numbers of dissimilar attributes. This approach is also better when you need to add/delete attribute types. If you have *very* dissimilar attributes and sets of attributes for each item then you might want to store the data in a serialized form in a blob field. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: how to prevent from Creating Large Numbers of Tables ???
thx for replying it's really a new course for me option 1 will still make lots of tables does option 2 waste of space ? cause 2 id with only 1 value i'm going to make table as described below. i wonder it's feasibility also bring another question here: my forum has favorite table for use to collect their favorite post Table `favorite`: cols: userid INT, postid INT unique index(userid, postid) only a pair of ID pre row question: does this struct waste of space? if so, any better solution ? - struct of product attribute: - Table `cate`: cols: cateID cateName example row: 1 switches-100 Table `Attribute`: cols: cateID attributeID attributeName displayOrder example row: 1 1 color 1 1 2 size 2 Table `product`: cols: cateID productID productName productDescription example row: 1 1 greenswitch blah blash... Table `AttributeValue`: cols: productID attributeID attributeValue(varchar) example row: 1 1 green 1 2 10*10 - to delete attribute: DELETE FROM Attribute where attributeID=$id DELETE FROM AttributeValue where attributeID=$id On Tue, 2002-06-18 at 12:28, Xuefer wrote: SET? i know how to use SET but... attribute of products is value such as Size Weight Color and so on the problem is, different CATE of products have different bunch of attribute Heh. Sorry - should have read more carefully. On Tue, 2002-06-18 at 00:53, Xuefer wrote: (mailbox full? repost) I have many product CATE (category) and every CATE of products have different attribute how can I orgnize table struct ? each CATE a table with many cols for attribute? [cateid attr1 attr2 ... ] or get attribute in one table and each row one attribute [produceID attributeID attributeValue ] or any other ways? Option one is good if you have a small number of attributes that are not likely to change. A major drawback is that you will need to alter the table to add/delete additional properties. Option two is more flexible, and will be more efficient for large numbers of dissimilar attributes. This approach is also better when you need to add/delete attribute types. If you have *very* dissimilar attributes and sets of attributes for each item then you might want to store the data in a serialized form in a blob field. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: how to prevent from Creating Large Numbers of Tables ???
On Tue, 2002-06-18 at 15:42, Xuefer wrote: thx for replying it's really a new course for me option 1 will still make lots of tables does option 2 waste of space ? cause 2 id with only 1 value How many tables are created and how much spaces is wasted depends on the data and the design. Test a few different designs - see how much space the designs take and how complex the queries are. i'm going to make table as described below. i wonder it's feasibility also bring another question here: my forum has favorite table for use to collect their favorite post Table `favorite`: cols: userid INT, postid INT unique index(userid, postid) only a pair of ID pre row question: does this struct waste of space? if so, any better solution ? This is probably the best way to handle it. - struct of product attribute: - Table `cate`: cols: cateID cateName example row: 1 switches-100 Table `Attribute`: cols: cateID attributeID attributeName displayOrder example row: 1 1 color 1 1 2 size 2 Table `product`: cols: cateID productID productName productDescription example row: 1 1 greenswitch blah blash... Table `AttributeValue`: cols: productID attributeID attributeValue(varchar) example row: 1 1 green 1 2 10*10 - to delete attribute: DELETE FROM Attribute where attributeID=$id DELETE FROM AttributeValue where attributeID=$id You might want to associate the attributes with categories in a different table. This way you could put an attribute into multiple categories. You could also specify the displayORder in the category table as well. Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: Re: how to prevent from Creating Large Numbers of Tables ???
sorry, i can't quite get what u meant dunno how to do as u said You might want to associate the attributes with categories in a different table. This way you could put an attribute into multiple categories. You could also specify the displayORder in the category table as well. On Tue, 2002-06-18 at 15:42, Xuefer wrote: thx for replying it's really a new course for me option 1 will still make lots of tables does option 2 waste of space ? cause 2 id with only 1 value How many tables are created and how much spaces is wasted depends on the data and the design. Test a few different designs - see how much space the designs take and how complex the queries are. i'm going to make table as described below. i wonder it's feasibility also bring another question here: my forum has favorite table for use to collect their favorite post Table `favorite`: cols: userid INT, postid INT unique index(userid, postid) only a pair of ID pre row question: does this struct waste of space? if so, any better solution ? This is probably the best way to handle it. - struct of product attribute: - Table `cate`: cols: cateID cateName example row: 1 switches-100 Table `Attribute`: cols: cateID attributeID attributeName displayOrder example row: 1 1 color 1 1 2 size 2 Table `product`: cols: cateID productID productName productDescription example row: 1 1 greenswitch blah blash... Table `AttributeValue`: cols: productID attributeID attributeValue(varchar) example row: 1 1 green 1 2 10*10 - to delete attribute: DELETE FROM Attribute where attributeID=$id DELETE FROM AttributeValue where attributeID=$id Good Luck! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
