Re: Database design - help
Again, please forgive my total ignorance. My ERD shows that the web links (URL table) are connected, via the sub-categories (SubCat table), to the main categories (Categories table). Is this correct for what I am trying to achieve ? Or should I also link the URL table to the Categories table ? Have been trying to create the ER Diagram with MySQL Workbench, and getting very frustrated. So far I have this www.ProBowlUK.co.uk/files/ERD_00.pdf cheers - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID(concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.77/2346 - Release Date: 09/04/09 17:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database design - help
Thanks Not sure how I'm reading this, but shouldn't the URL be linked to SubCategory ? - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID (concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: John Meyer john.l.me...@gmail.com To: BobSharp bobsh...@ntlworld.com Cc: mysql@lists.mysql.com Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- Windows Live: Keep your friends up to date with what you do online. Find out more
Re: Database design - help
Please forgive my total ignorance. URL-Category linking ... with Foriegn Keys or Primary Keys ? Have been trying to create the ER Diagram with MySQL Workbench, and getting very frustrated. cheers - Original Message - From: Martin Gainty To: bobsh...@ntlworld.com ; mysql@lists.mysql.com Cc: john.l.me...@gmail.com Sent: Friday, September 04, 2009 6:09 PM Subject: RE: Database design - help given the following table layouts URLs: URL_ID (primary key for URL) URL_TEXT URL_CATEGORY URL_ID (key which points to URL.URL_ID) CATEGORY_ID (key which points to CATEGORY.CATEGORY_ID) SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) CATEGORY CATEGORY_ID (primary Key for Category) CATEGORY_TEXT SUBCAT SUBCAT_ID (concatenated key for SubCat) CATEGORY_ID (concatenated key for Subcat) SUBCAT_TEXTso the diagram would look something like like URL_CATEGORY Table (URL Table) (CATEGORY TABLE) URL_ID1-1 URL.URL_ID CATEGORY.CATEGORY_ID1---1CATEGORY_IDURL_TEXT 1 ↓ 1 SUBCAT.CATEGORY_ID SUBCAT.SUBCAT_TEXT this is labour-intensive work that every DBA must perform to create a Database Martin Gainty __ From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: john.l.me...@gmail.com Subject: Re: Database design - help Date: Fri, 4 Sep 2009 16:24:22 +0100 Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Database design - help
Hi Thanks for all the responses. However I am still stuck for a MySQL db I can create and code in PHP. Attached is a brief example of data to be used. One problem I have is with providing a listing that includes ... WTBC (Category without SubCats) and the 3 Zones (also, Cats without SubCats ??? ) (This is for a complete WTBC listing, in practice it may list depending on selected Zone) The example Schema is interesting, but is there another way of storing all links in one table and join them to Category and SubCat tables ? An example of the ER Diagram would also be helpful to me. cheers - Original Message - From: John Meyer john.l.me...@gmail.com To: BobSharp bobsh...@ntlworld.com Cc: mysql@lists.mysql.com Sent: Monday, August 31, 2009 4:56 PM Subject: Re: Database design - help BobSharp wrote: As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? Here's one suggestion Table: URLs: URL_ID URL_TEXT CATEGORY CATEGORY_ID CATEGORY_TEXT SUBCAT SUBCAT_ID CATEGORY_ID SUBCAT_TEXT URL_CATEGORY URL_ID CATEGORY_ID SUBCATEGORY_ID PK: (URL_ID, CATEGORY_ID) No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.72/2337 - Release Date: 08/31/09 05:50:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13901 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database design - help
As a complete newbie in MySQL, I need a database to store URLs related to Tenpin Bowling. There are several Categories ... Equipment Manufacturers, Organistations, (UK) ProShops, (UK) Bowling Centres, Personal Websites, Misc., Coaching Instructional websites, etc. There will be some sub-categories. eg: Organistions will have ... Zones of WTBC, National Organisations within the Zones, UK organisations, Disabled Bowling organisations, ... eg: Personal Website might have ... Bowler's, Pro Bowler's, Leagues, etc. Can anyone suggest how I should set out tables for this database ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 13876 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie ... Schema details listing
I have written a .cfm to output a listing of columns with Name, Format, Size, NULL, KEY, etc. One thing I need to include is whether the Primary Key is AUTO_INCREMENT That element does not appear to be in the same SCHEMA table. Where is it ? and How can I use it with this Query ... ? CFQuery datasource=bs3578 name=pColumns SELECT COLUMN_NAME AS pCname, DATA_TYPE AS pDtype, IS_NULLABLE AS pISnull, CHARACTER_MAXIMUM_LENGTH AS pMAXlen, COLUMN_DEFAULT AS pCdefault, COLUMN_TYPE AS pCtype, COLUMN_KEY AS pCkey, NUMERIC_SCALE AS pNscale, NUMERIC_PRECISION AS pNprec FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = Products AND TABLE_SCHEMA = bs3578; /cfquery -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Newbie --- JOINS
Picture does not seem to have been carried in the message, posts with attachment did not seem to get through either. So hope the link works. Below is the ER diagram in an exercise I am trying to do. http://www.probowluk.co.uk/images/er_ECA_001.jpg It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database --- no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Newbie and JOINS
Below is the ER diagram in an exercise I am trying to do. It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database = no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
MySQL - facts
Need some facts for a Report ... For a hypothetical Kitchen Company's shopping Catalogue. a.. What advantages does a database have over flat-file systems ? b.. Are there any disadvantages in switching to a database solution ? c.. What is MySQL, and how does it relate to ASP, Javascript/VBscript and flat-files ? d.. Are there any alternatives to MySQL ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12840 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Negated SELECT query
3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Negated SELECT query
Thanks for that,worked through and found that this gives the correct result ... --- Employee No Assets --- SELECT DISTINCT e.employeeID AS eID, concat(e.firstname, , e.lastname) AS eName FROM employees e LEFT JOIN assets a ON e.employeeID = a.employeeID WHERE e.employeeID IS NULL ORDER BY e.employeeID --- Employee No History --- SELECT DISTINCT a.assetID AS aCode, LEFT(a.assetdescription,60) AS aTitle, c.assetcategory AS cCategory FROM assets a LEFT JOIN maintenance m ON m.assetID = a.assetID LEFT JOIN assetcategories c ON a.assetcategoryID = c.assetcategoryID WHERE m.assetID IS NULL ORDER BY a.assetID Cheers - Original Message - From: Bonnett, John john.bonn...@vision.zeiss.com To: bobsh...@ntlworld.com; mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Sent: Tuesday, March 17, 2009 10:59 PM Subject: RE: Negated SELECT query SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobsh...@ntlworld.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.237 / Virus Database: 270.11.18/2008 - Release Date: 03/17/09 16:25:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT with JOINs
This links to an ERD diagram that illustrates a MySQL database schema. www.ProBowlUK.co.uk\images\ERD_001.jpg They are MyISAM tables, with no Foreign Key contraints (the fk suffix is not used in the actual database). I need to provide MySQLSELECT statements, with various combinations of JOINs, to give the following output ... 1) Who is assigned what assets? 2) What maintenance has been carried out on each asset? 3) Which assets have not undergone any maintenance? 4) Who hasn't been assigned any assets? 5) Which assets have not been scheduled for maintenance? 6) Which employees have been assigned assets for which no maintenance has been scheduled? I have managed to complete the first 2. Making no headway at all with the others. Can anyone give me pointers/examples ? Also, in the first SELECT, is there a way of not repeating an employee's name in the output ? -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12716 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
