RE: Design Question
EB My thought was to add a new field to the listings table that would EB contain a comma-separated list of CategoryIDs, but something doesn't EB feel right about this solution. This would break the first normalization form and is extremely bad Okay - I thought something was off... First of all ask your customer - what is the relation between listings and categories - is it one-to-many or many-to-one or many-to-many relation The current relationship is one to one - each listing can only have one category. The customer is requesting a change to this, so that each listing can have many categories. if it is one-to-many (many-to-one) then you should add a field to details table that constitutes a primary key in the main table and define a foreign key. That means having either CategoryID in listings table or ListingID in categories table. My current table definition for the listings already has the foreign key of CategoryID. What you are saying is that the categories table should have a field for ListingID? Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
You are right, a comma separated list won't work since you won't be able to do joins on it. To create a one to many relation, you actually need to create another table to hold the relation. CREATE TABLE listCatLink ( ListingID bigint(20) unsigned NOT NULL, CategoryID int(11) NOT NULL ) On Aug 4, 2004, at 10:35 AM, Erich Beyrent wrote: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain
RE: Design Question
I think you understand. Here is how I would re-write the query to use the new table: $query = SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description FROM listings l INNER JOIN publishers p ON l.PublisherID=p.PublisherID INNER JOIN composers c ON l.ComposerID=c.ComposerID INNER JOIN arrangers a ON l.ArrangerID=a.ArrangerID INNER JOIN listings_categories lc ON l.ListingID = lc.ListingID INNER JOIN categories o ON lc.CategoryID = o.CategoryID WHERE o.Name='.$Category.' ORDER BY .$OrderBy; (That's just the style I prefer as I can more easily spot which match-up conditions belong to which sets of tables. That way I am less likely to leave one out and accidentally create a cartesian product of any two tables. The comma separated style you use is absolutely, perfectly valid.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 12:39:55 PM: I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote
Re: Design question from newbie
Andrew Ward writes: The only way I can see of dealing with this is to create tables like ID,QUESTION,RESPONSE 1,YEAR,2001 1,SEX,1 1,AGE,3 1,RATING A, 7 1,RATING B, 6 ... This doesn't strike me as very smart. I would greatly value anyone's advice on dealing with data that is similar in many respects but different in others. Thank you very much. If you want it to be fairly general you could have a table with possible value types and then another table with the actual values. It'll require more space and you might want to have one table for numbers and another for strings, but if there are a lot of different fields and most of them don't appear in the majority of the entries (the rows in your example), doing it like that may be motivated. It'd make some searches less convenient, but on the other hand it'll be much easier to add new fields. Today I went from having a table with 38 rows for each line in an input file (which was output from some software), to having a single 40-column row per line. This will be less convenient to handle as it's muss less general, but on the other hand I saved some much-needed space. (Some numbers: old: 12 bytes per row (19 with overhead), times 38 rows = 722 bytes. new: 100 or 133 bytes. Multiply with 3 per input file and 1000 for the number of input files within the first year or two. Eek.) -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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: Design question from newbie
Andrew Ward wrote: As I said, not all organisations were asked the same questions so I can't just put the data in directly The only way I can see of dealing with this is to create tables like ID,QUESTION,RESPONSE 1,YEAR,2001 1,SEX,1 This doesn't strike me as very smart. I would greatly value anyone's advice on dealing with data that is similar in many respects but different in others. Thank you very much. Actually it is not that bad of an idea esp. since you have different questions. You might make two tables: one to hold common information and the other to hold the questions, e.g: CREATE TABLE header ( question_id integer, year integer, age integer, sex char, UNIQUE( question_id)); CREATE TABLE questions ( question_id integer, question char(32), response_str char(32), response_int integer, KEY( question_id )); The whole response_str/response_int thing is only if you have lots of questions and do not want to calculate stats on cast strings. If your responses are always ints then, obviously, you would not need the _str/_int thing either. With a design like this you can start to get stats in a clean way, e.g.: How may male respondents questioned in and after 2000 liked beer? SELECT COUNT(*) FROM header, questions WHERE header.question_id=questions.question_id AND year=2000 AND sex='M' AND question=LIKES BEER AND response_str=Y; You can count me as one. ;-) --Bill - 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: Design question
This looks like an order-lineitem situation, which I would imagine would look like this: ORDERS ORDER_ID CUSTOMER_ID ORDER_DATE LINEITEM ORDER_ID PRODUCT_ID LINEITEM_QUANTITY At 02:00 PM 6/18/2001 -0500, you wrote: I have a design problem I was hoping some of you could help me out with. I am writing a shopping cart system (as if there wasn't enough already) and I have one problem that has me stumped. A person can order more than one item of course. So let's say they order a kite and some string. Both of these have product numbers, and the overall order has an order number. I am trying to figure out how to design a table ( or tables ) so I can keep track of the different products that are ordered during one order. I need to keep track of the product number, quantity, and price for that product. For instance: Order # Products - -- 1 222 : Qty 1: 12.00 # 333 : Qty 2 : 14.00 That is order number 1 and that person ordered one of product # 222 @ 12.00 and 2 of product # 333 @14.00. I would appreciate any assistance any of you might be able to offer. If I wasn't clear enough, let me know and I will try and explain it better. Thanks. Jeff Jones - 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 John Meyer [EMAIL PROTECTED] Programmer If we didn't have Microsoft, we'd have to blame ourselves for all of our programs crashing - 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: Design question
hi. the easiest way is to have an order table: (orderID, customer, address, etc., etc.) a item table: (itemID, description, price, etc.etc.) and an orderItems table: (orderID, itemID) this way you can select i.description, i.price from order o, item i, orderItems oi, where o.orderID = 1 and o.orderID = oi.orderID and oi.itemID = i.itemID or something like that. you could replace the customer/address columns in order with a customerID referencing a customer table as well... -ravi. -Original Message- From: Jeff Jones [mailto:[EMAIL PROTECTED]] Sent: Monday, June 18, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Design question I have a design problem I was hoping some of you could help me out with. I am writing a shopping cart system (as if there wasn't enough already) and I have one problem that has me stumped. A person can order more than one item of course. So let's say they order a kite and some string. Both of these have product numbers, and the overall order has an order number. I am trying to figure out how to design a table ( or tables ) so I can keep track of the different products that are ordered during one order. I need to keep track of the product number, quantity, and price for that product. For instance: Order # Products - -- 1 222 : Qty 1: 12.00 # 333 : Qty 2 : 14.00 That is order number 1 and that person ordered one of product # 222 @ 12.00 and 2 of product # 333 @14.00. I would appreciate any assistance any of you might be able to offer. If I wasn't clear enough, let me know and I will try and explain it better. Thanks. Jeff Jones - 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: Design question
Hi Jeff, If you visit my homepage at: www.geocities.com/hisiomara click Tutorials and select the first item Team5.com an ecommerce sample you will be able to see the ERD diagram that attends your ecommerce demand. Actually will will be able to see much more. The design is there,and I will post the codes after. Although I implemented in Oracle,the design is neutral and can be implemented in any database. Please let me know your opinion about it, I do appreciate it. Take care Sio PS: I would like to invite everybody to go there. This is a site intended to show my skills but I also share good stuffs with everybody. From: Jeff Jones [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Design question Date: Mon, 18 Jun 2001 14:00:29 -0500 I have a design problem I was hoping some of you could help me out with. I am writing a shopping cart system (as if there wasn't enough already) and I have one problem that has me stumped. A person can order more than one item of course. So let's say they order a kite and some string. Both of these have product numbers, and the overall order has an order number. I am trying to figure out how to design a table ( or tables ) so I can keep track of the different products that are ordered during one order. I need to keep track of the product number, quantity, and price for that product. For instance: Order #Products - -- 1 222 : Qty 1: 12.00 # 333 : Qty 2 : 14.00 That is order number 1 and that person ordered one of product # 222 @ 12.00 and 2 of product # 333 @14.00. I would appreciate any assistance any of you might be able to offer. If I wasn't clear enough, let me know and I will try and explain it better. Thanks. Jeff Jones - 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 _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
