Design help
Hi I'm creating the following basic tables COUNTRIES countries_id name REGIONS region_id countries_id name CITIES cities_id region_id Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? Thanks Neil
Re: Design help
Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design help
Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? On Sun, Apr 21, 2013 at 9:28 AM, Denis Jedig d...@syneticon.net wrote: Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/**Denormalizationhttp://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Design help
Hello, Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? I do not think you need surrogate key for country. Country code is 2 letters, so if you use char(2) charset ASCII you only need 2 bytes for that. That is ok and your queries would be easier to read. You do not need surrogate keys for US states also but if you speak about states in general you may need key (many countries over the world have states) The same is about cities: city name is too big to be used as primary key, and there may be many cities with similar names. Ilya. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Table design help
At 04:23 PM 9/9/2010, Tompkins Neil wrote: Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil Neil, If you are just starting out with database design, you may get some ideas from http://www.databaseanswers.org/data_models/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table design help
Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil
Fwd: Database design help
Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil
Re: Database design help
Hi there, I know you would like just a solution, but I want to give you just a little bit of background. Think in real life things(entities), think as you would have to do it on paper. [1] You said you have: managers, teams players and fixtures/results (matches) these are your tables plus teams! [2] then find what relationships you have between tables. (a) *manager* (have name and other properties) belongs to a team (b) *player* (have name and other properties) belong to a team (c) *team* (have name , have 1 manager, and other properties) play matches (d) *match* have results translate [have] with properties(columns with values of the table) translate other actions with relationships (columns with ids of other tables) in the case of *match* you would have: idteam1, idteam2, result at least. in the case of *player* you would have: idplayer, name, idteamat least in the case of *manager*, if you think a manager can manage more than one team you will use an idmanager in the team table otherwise you can use also an idteam in the manager table that allows more managers to manage the same team. I dont want (no time sorry!) to write here the data model, but I think this few lines can trigger the best idea in you. Claudio 2010/9/1 Tompkins Neil neil.tompk...@googlemail.com Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- Claudio
RE: Database design help
Hi Neil, May be your question is too vague. You have already identified the 'real world' objects that you want represented in the database. Have you identified the specific pieces of information that you want stored for each object ? After you do that, you can then start to see what the relationships between the objects are. And you can then ask people that don't know anything about your application more specific questions like: - this is what I have in this and that object : how do I get this and that to relate to this and that ? Thanks, Justin -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: 01 September 2010 12:52 To: [MySQL] Subject: Fwd: Database design help Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil __ Information from ESET NOD32 Antivirus, version of virus signature database 5414 (20100901) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5414 (20100901) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- 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
I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- 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
I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
Re: Database design help
On 9/1/2010 11:47 AM, Tompkins Neil wrote: I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? I think you are definitely on the right track. Each score does not belong to just a manager or to a team but to a manger/team combination. Should the manager switch teams, those results need to remain associated to both entities not just the manager. here's a possible record shape: manager_id, team_id, game_id, ... summary details about the game ... This way each result is associated with the correct combination of entities (a manager and a team) and not just one or the other. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- 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
Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- 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
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
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_TEXT so 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. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009
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
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) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Design Help Needed
HI Sudheer, THIS DESIGN IS BASED ON MY MANY-TO-ONE DIRECTION PATTERN OF DATABASE NORMALIZATION DESIGN... PLEASE MODIFY/CORRECT IT ACCORDING TO YOUR TASTE. AS A JAVA DEVELOPER I'M USING HIBERNATE FOR MY CREATE-UPDATE-DELETE(CUD) AND DIRECT JDBC FOR MY QUERIES(R). YOU MAY VARY. HERE, HOPE THIS WILL GIVE YOU AN IDEA. LEGEND: X-TABLE, L-LONG, S-STRING/CHAR(?), T-DATETIME _ID-PRIMARY_KEY, REF-FOREIGN_KEY X:CONTACTS_AND_ADDRESSES L:CONTACT_AND_ADDRESS_ID S:WEBSITE S:EMAIL S:MESSAGING S:TELEPHONE S:MOBILE S:LAND_ADDRESS T:CREATED/MODIFIED X:INDIVIDUALS L:INDIVIDUAL_ID S:USERNAME S:PASSWORD S:DISPLAY_NAME S:PICTURE_URI S:SECURITY_QUESTION S:ANSWER T:CREATED/MODIFIED X:INDIVIDUALS_CONTACTS_AND_ADDRESSES L:INDIVIDUAL_CONTACT_AND_ADDRESS_ID L:REF_INDIVIDUAL_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS L:ESTABLISHMENT_ID S:BUSINESS_NAME T:CREATED/MODIFIED X:ESTABLISHMENTS_CONTACT_AND_ADDRESS L:ESTABLISHMENT_CONTACT_AND_ADDRESS_ID L:REF_ESTABLISHMENT_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS_OF_INDIVIDUALS L:ESTABLISHMENT_OF_INDIVIDUAL_ID L:REF_ESTABLISHMENT_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:CUSTOMERS L:CUSTOMER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:PARTNERS L:PARTNER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:EMPLOYEES_POSITIONS L:EMPLOYEE_POSITION_ID S:ROLE S:SPECIFIC_RULE S:HOWTODO T:CREATED/MODIFIED X:TIME_SCHEDULES L:TIME_SCHEDULE_ID S:APPLIED_TASK T:APPLIED_TIME L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED X:EMPLOYEES L:EMPLOYEE_ID L:REF_INDIVIDUAL_ID L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED GOOD LUCK, HOPE THIS HELPS. -Melvin Sudheer Satyanarayana [EMAIL PROTECTED] wrote: Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
RE: Design Help Needed
Hi Sudheer, First of all there a number of ways to design this database. You will need to choose the one that you feel suites your needs best. Here one possible design. Because you have different type of users/accounts, it looks like ACCOUNT_TYPE table is needed ACCOUNT_TYPE table Account_type_id - autoincrement, PK Account_type - varchar(25). ( Values in this field are: Individual,Business,Partner,Internal) Because you have user information to store: USER_INFO table User_id - autoincrement, PK Account_type_id - integer, FK (foreign key to ACCOUNT_TYPE) Fields of personal/Business information to follow - (First, Last Name, .) Because you have web site security info to store: WEB_SITE_SECURITY_INFO table User_id - integer, FK (foreign key to USER_INFO) Security_question Security_question_answer Fields of security information to follow You can grow the database as you add module, but as much as you can foresee and design ahead it would be better Mikhail Berman -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Help Needed
Because you are a novice to data base design, you have fallen into a common trap. If you think about an array, you don't want to store multiple users in a row, you want to store them in a column. In other words, you want to have one table that stores businesses (once per business, probably) and another table that stores the associated users. You use a unique business ID to find all of the users for a particular business. Once you start thinking that way, you'll start to get the hang of it. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design Help Needed
Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database design help
A lot depends upon the sophistication of the program you write to manage this. I doubt there is any way to create something so sophisticated with just SQL. My first thought would be to use three tables. Make sure every user has a unique use ID. The users' passwords would be stored in the table of users (I'm assuming that the passwords are unique to users, rather than groups.) user_id autoincrement user_name user_pass User other information Each group would also have a unique group id. The table of groups would only contain three fields: group_id autoincrement owner's user id group_name This lets you find each user's owned groups. The name field is so that a user can readily see which group is which in a human-readable way. Then you want a table of group members, again with only two fields: group_id member_id Now to find a user's groups, you look for the user_id in the group table. To find its members, you look in the group member table. You can also work backwards to find all of the groups that a user belongs to by starting from the other direction. The password checking for managing a user's groups would be at the application level. You'd have one record Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: ppywriw [mailto:[EMAIL PROTECTED] Sent: Saturday, December 16, 2006 11:54 AM To: mysql@lists.mysql.com Subject: database design help Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design help
Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design help
At 12:54 PM 12/16/2006, you wrote: Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. Presumably users is something like this: Members table ID - numeric, auto-generated BelongsTo - group id Firstname Surname email phone etc. But that's not right - A User can belong to only one group, most probably want to belong to many - so remove the BelongsTo field and let a refernce to the ID field of the membertable do the work GroupMembers table ID - numeric, autogenerated Group_ID - numeric, foreign key Member_ID - numeric, foreign key - refers to ID field in Members table and of course a Groups table, ID - numeric, autogenerated Managed_By - foreign key, refers to ID field in Members table Name other pertinent stuff There you go - three tables able to hold unlimited combinations of groups and members and you will never have a many to many problem. Cheers - Miles other info . -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.21/589 - Release Date: 12/15/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design help
Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
- Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Friday, February 17, 2006 7:49 AM Subject: Database design help Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When you say joining table, I assume you mean an intersection table or association table, which are the more common terms that describe something used to break a many-to-many relationship into two one-to-many relationships. I've never heard it described as a joining table but I _think_ we're talking about the same thing In any case, I don't think you need anything but the two tables you have here. If there is only ever going to be a single affiliate assigned to a given visitor, then this is a one-to-many relationship and there is no need for an additional table. However, I would suggest one small amendment to your visitors table. Add the clause: FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A DELETE RULE HERE This will ensure that you never add an affid other than a value found in the Affiliates table to the affid column of the visitors table. It will also ensure the proper behaviour when deletes take place in the affiliates table. For example, if you use ON DELETE CASCADE as your delete rule, if one of the affliates is deleted from the affiliates table, all of the rows with his ID will also be deleted from the visitors table. If you use ON DELETE RESTRICT, you will not be able to delete an affiliate from the affiliates table unless all of the Visitors rows with his ID have had their affid changed to that of some other affiliate. If you use on DELETE SET NULL, you can freely delete affiliates even if they have rows in the Visitors table; the Visitors rows will just have their affids set to null, which effectively means that those Visitors have no assigned affiliate. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 16/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
OK, I think I got it now. Thanks for the additional info, that helps alot. Rhino wrote: - Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Friday, February 17, 2006 7:49 AM Subject: Database design help Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When you say joining table, I assume you mean an intersection table or association table, which are the more common terms that describe something used to break a many-to-many relationship into two one-to-many relationships. I've never heard it described as a joining table but I _think_ we're talking about the same thing In any case, I don't think you need anything but the two tables you have here. If there is only ever going to be a single affiliate assigned to a given visitor, then this is a one-to-many relationship and there is no need for an additional table. However, I would suggest one small amendment to your visitors table. Add the clause: FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A DELETE RULE HERE This will ensure that you never add an affid other than a value found in the Affiliates table to the affid column of the visitors table. It will also ensure the proper behaviour when deletes take place in the affiliates table. For example, if you use ON DELETE CASCADE as your delete rule, if one of the affliates is deleted from the affiliates table, all of the rows with his ID will also be deleted from the visitors table. If you use ON DELETE RESTRICT, you will not be able to delete an affiliate from the affiliates table unless all of the Visitors rows with his ID have had their affid changed to that of some other affiliate. If you use on DELETE SET NULL, you can freely delete affiliates even if they have rows in the Visitors table; the Visitors rows will just have their affids set to null, which effectively means that those Visitors have no assigned affiliate. -- Rhino -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also
Re: Database design help
Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm
Re: Database design help
Rhino, I appreciate your comments. This wasn't meant to be a real world example. My actual application keeps track of changing data in a gas network. I wanted to simplify the problem to help in finding an answer to my dilemmas. Ian At 04:45 PM 1/20/2006 -0500, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +, Marco Neves wrote: Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost
Re: Database design help
Perhaps you could use database triggers to keep track of these changes. I also think there's a way you could make the change tracking a little neater if you don't mind sacrificing some SQL functionality and storing everything as text. If you create a table product_changes, with 4 columns - id, change_date, change_type, and current_value, you could add one or more entries each time or more aspects of your product change. If your salesrep changes, set your change_type = salesrep If your color changes, set your change_type = color. If your size changes, set your change_type = size. If nothing changes, then add no entry. You could have the database track this for you with triggers, so you wouldn't even have to do . I haven't used triggers with MySQL so I'm sure my syntax is off but here's a rough outline of what you could do create trigger ProductUpdateTrig on Product for update as begin if (newsalesrep != oldsalesrep) insert into product_changes values (now(), 'salesrep', oldsalesrep) if (newcolor != oldcolor) insert into product_changes values (now(), 'color', oldcolor) if (newsize != oldsize) insert into product_changes values (now(), 'size', oldsize) end If you wanted to keep track of additions and deletions you could create similar insert and delete triggers. Note that you would likely have to store the values as text even if they were originally numeric or DATETIME, in order to be able to use a simple table to keep track of all the different kinds of changes. Hope this helps, Dan At 4:45 PM -0500 1/20/06, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30
Re: Database design help
Hi Dan, It would be a pretty large table of changes, but this solution would work, if as you say, I don't mind making those sacrifices. Something to think about. Thanks. Ian At 03:59 PM 1/20/2006 -0600, Dan Buettner wrote: Perhaps you could use database triggers to keep track of these changes. I also think there's a way you could make the change tracking a little neater if you don't mind sacrificing some SQL functionality and storing everything as text. If you create a table product_changes, with 4 columns - id, change_date, change_type, and current_value, you could add one or more entries each time or more aspects of your product change. If your salesrep changes, set your change_type = salesrep If your color changes, set your change_type = color. If your size changes, set your change_type = size. If nothing changes, then add no entry. You could have the database track this for you with triggers, so you wouldn't even have to do . I haven't used triggers with MySQL so I'm sure my syntax is off but here's a rough outline of what you could do create trigger ProductUpdateTrig on Product for update as begin if (newsalesrep != oldsalesrep) insert into product_changes values (now(), 'salesrep', oldsalesrep) if (newcolor != oldcolor) insert into product_changes values (now(), 'color', oldcolor) if (newsize != oldsize) insert into product_changes values (now(), 'size', oldsize) end If you wanted to keep track of additions and deletions you could create similar insert and delete triggers. Note that you would likely have to store the values as text even if they were originally numeric or DATETIME, in order to be able to use a simple table to keep track of all the different kinds of changes. Hope this helps, Dan At 4:45 PM -0500 1/20/06, Rhino wrote: Ian, If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it. My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about. For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail. I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems. I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a wouldn't it be nice if we could track XXX? remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality? If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle. Just a general observation made by a disinterested third party; ignore it if you like :-) Rhino - Original Message - From: Ian Klassen [EMAIL PROTECTED] To: Marco Neves [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20
Database design help
Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02 | 100 | 50| 25 2006-01-03 | NULL | 45| NULL 2006-01-05 | 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date | end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02 | 2006-01-03| 100 | TRUE | 50 | TRUE | 25| TRUE 2006-01-03 | 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05 | 2006-01-05| 78| TRUE | 45 | FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Hi, Why don't you create two table: * a product table, with the product discriptions, and other product related info (call it prod): |ID|NAME|SOME|OTHER|FIELDS| |1|ProdA|..|..|..| |2|ProdB|..|..|..| * a stock movements table, with moviments by product (call it pro_move): |ID|PROD__ID|DAY|MOV|DESCRIPT| |1|1|2006-01-01|10|Inventory at Jan 1st for Prod A| |2|2|2006-01-01|25|Inventory at Jan 1st for Prod B| |3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd| Then to know the inventary to up-to-date of every product you can do: SELECT p.id,p.name,sum(pm.mov),max(day) FROM prod p LEFT JOIN prod_move pm ON p.id=pm.prod__id GROUP by p.id; If you think your product or move table will grow too big you can add a stock column to the prod table and update that field when you add a movement to your prod_move table, and verify that value from time to time (and if possible just add movement in transaction, with both tables suporting them - InnoDB ou DBD). This is the way I would do it. What you think? mpneves On Wednesday 18 January 2006 18:09, Ian Klassen wrote: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02| 100 | 50| 25 2006-01-03| NULL | 45| NULL 2006-01-05| 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02| 2006-01-03| 100 | TRUE | 50 | TRUE | 25| TRUE 2006-01-03| 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE | 45 | FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Ian Klassen [EMAIL PROTECTED] wrote on 01/18/2006 01:09:55 PM: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02 | 100 | 50 | 25 2006-01-03 | NULL | 45 | NULL 2006-01-05 | 78 | NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date | end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02 | 2006-01-03 | 100 | TRUE | 50 | TRUE | 25 | TRUE 2006-01-03 | 2006-01-05 | 100 | FALSE | 45 | TRUE | 25 | FALSE 2006-01-05 | 2006-01-05 | 78 | TRUE | 45 | FALSE | 22 | TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian Something you didn't think of: CREATE TABLE physical_inventory ( date_of_inventory datetime, product_id int unsigned, quantity int, PRIMARY KEY(product_id, date_of_inventory) ) Then determinining the current inventory is a simple two-step process (also known as finding the groupwise maximum): A) find the latest date_of_inventory for each product: CREATE TABLE tmpInv(KEY(product_id, date_of_inventory)) SELECT product_id, max(date_of_inventory) date_of_inventory FROM inventory GROUP BY product_id; B) rejoin to your original table to get the quantity SELECT ti.product_id, ti.date_of_inventory, i.quantity FROM tmpInv ti INNER JOIN inventory i ON ti.product_ID = i.product_id AND ti.date_of_inventory = i.date_of_inventory; DROP TABLE tmpInv; With this design, you won't have an inventory table of several hundred columns and you won't need to change your database design every time a product is added or removed from inventory. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Database design help
At 06:27 PM 1/18/2006 +, Marco Neves wrote: Hi, Why don't you create two table: * a product table, with the product discriptions, and other product related info (call it prod): |ID|NAME|SOME|OTHER|FIELDS| |1|ProdA|..|..|..| |2|ProdB|..|..|..| * a stock movements table, with moviments by product (call it pro_move): |ID|PROD__ID|DAY|MOV|DESCRIPT| |1|1|2006-01-01|10|Inventory at Jan 1st for Prod A| |2|2|2006-01-01|25|Inventory at Jan 1st for Prod B| |3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd| Then to know the inventary to up-to-date of every product you can do: SELECT p.id,p.name,sum(pm.mov),max(day) FROM prod p LEFT JOIN prod_move pm ON p.id=pm.prod__id GROUP by p.id; If you think your product or move table will grow too big you can add a stock column to the prod table and update that field when you add a movement to your prod_move table, and verify that value from time to time (and if possible just add movement in transaction, with both tables suporting them - InnoDB ou DBD). This is the way I would do it. What you think? mpneves On Wednesday 18 January 2006 18:09, Ian Klassen wrote: Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory | product a | product b | product c 2006-01-02| 100 | 50| 25 2006-01-03| NULL | 45| NULL 2006-01-05| 78| NULL | 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date | product a | pa_up | product b | pb_up | product c | pc_up 2006-01-02| 2006-01-03| 100 | TRUE | 50| TRUE | 25| TRUE 2006-01-03| 2006-01-05| 100 | FALSE | 45| TRUE | 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE | 45| FALSE | 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP Hi, The problem I run into then is that it's not just the inventory that changes. Each product has it's own set of custom fields that change with time. With this scenario, the stock movements table would require columns for each value that can change. Or I could create a table for each field that changes with time but that could get unwieldily very quickly. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE 2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE With this solution I can quickly retrieve the inventory on any given day and see what inventory was taken on that day (which product update columns are set to TRUE). However, I see the update side of this as a nightmare (especially considering I'm duplicating data). A third solution could be breaking each product into its own table. This would eliminate the issues with the first two solutions but I would end up with hundreds of tables which I would like to avoid. Any help on the direction that I should go would be greatly appreciated. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
Ian, I'ld like to help you, but a more specific db design would depend on more specific description on your application needs. What I can say is that you need to adapt your database to your reality. What I got til now is that you need a product table, where you can store your basic information on products. You say you have other information, but I could understand several things. 1- That other information is related to the product, to the transaction, to both, to stocks? for example, color or size is relevant to determine stocks and is related to the product, and so is relevant to the transactions also. The sale rep is relevant to transaction, but not to the product. sales rep comission is relevante to the sales rep, but not to the transaction nor the product. My point is, a database design can be a complex task, and the hability an application will have to provide solutions to the real world depends, before anyother thing in that database design. The is the point where almost all analisys most be done, and almost no programming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote: Thanks Ed. That's another good idea. The consensus I'm getting is to create one table that stores unchanging data about the product and another that stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value. As an example, let's say I want to keep track of not only the quantity of a product but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both in the same transaction table, but then I'll end up with duplicated data. For example, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: I built my inventory system like this, I have a products table that contains all the information specific to each part, less the quantity, i.e. Part Number, Description, Vendor, Color, Weight, SKU number, etc... Then I have another table that is my Inventory Tranactions Log that is just the following Date, ProductID, Qty, TypeOfTranacstion, Comment The inventory for each part may adjust daily or not. When parts are removed/sold the transaction log gets a record for that product and the number of parts that were sold and the type of transaction that occurred. When parts are received another transaction is entered for that part with the quantity received and the type of transaction that occurred. When we close the store and want to take a full inventory we first run a report that get the sums of all the transactions for each product and that tells us what should be on the shelf according to the database. Then we verify or adjust the qty for each product on the shelf by adding a record to the transaction log indicating the quantity and the type of transaction that occurred. When we want to see the values in the inventory its a very simple report to get the sums for each product. - Hope that helps. Ian Klassen [EMAIL PROTECTED] 1/18/06 10:09:55 AM Hi all, I'm trying to figure out a solution to the following problem. Let's say I have a store with various products. I take inventory of these products on different days. At any given time I want to view what the inventory is for the entire store. I also want to know whether the inventory in the result was taken on that day or was carried forward from a previous date. I may also have to make changes to the inventories previously recorded. I have a few solutions, none of which I'm really happy with. The first is to create rows that contain the inventory for each product on a given day. If no inventory was taken for a given product then leave the column null. date_of_inventory| product a| product b| product c 2006-01-02| 100| 50| 25 2006-01-03| NULL| 45| NULL 2006-01-05| 78| NULL| 22 To obtain the inventory on any given day I would have to query each product and find the most recent time that it was updated. With this solution there is a lot of wasted space caused by the NULL's. Another solution would be to have a start and end date for when the inventory is valid such as: start_date| end_date| product a| pa_up| product b| pb_up| product c | pc_up 2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE 2006-01-03|
Table design help
I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table design help
you are going to want a 'buster' table... also known as a many-to-many table so you have: contacts Contact_Key Industries - Industry_Key Contact_Industry_assoc -- Contact_Key Industry_Key -Original Message- From: DeBerry, Casey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 1:09 PM To: '[EMAIL PROTECTED]' Subject: Table design help I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design help
Casey, I would consider, on the most basic level, a method like this: Contacts (all the company contact info, etc) using primary key contact_id - IndustrialRelationships table - industry_id primary key on Industries table (all the different type of industries) So, in the IndustrialRelationships table, it's simply two columns... the contact_id for the contact, and the industry_id for the industry. Thus, you can put an arbitrary number of industries associated to a single contact. Then, later on, you can use this data to determine how many contacts you have for a given industry. To get the data back out, you could use a JOIN perhaps. This is very simple, but it should get you pointed in the right direction. I might suggest getting a book about or reading some pages regarding data modeling for more information on normalization. -- R. Deuce I have a small table that contains company contact information: Table: Contacts Key Name Address Phone Email Website etc.. What I need to do is associate the type of industry the business servers from a list of about 40 industries... My initial thinking was to create another table that contains all of the industries along with unique key for each. So table would look like Table: Industries Industry Key The problem is, some businesses server many different industries. So my question is, can I add another column to the contacts table that would associate many different industry key's to the contact? What is the best way to design this database to talk with PHP? Thanks, Casey DeBerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: content design / database design help
Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Let me add a bit to this Relations will make sense for items like 'project notes' which would work something like. table 'notes' project_id | create_date | note | author my tabledesign would be note_id(primary) | project_id(foreign) | create_date | note | author in order to have a primary that not only one note for each project is possible. table 'project_contacts' project_id | name | company | address | phone | description same here, add a contact_id :o) ... Though I'd go with the first advise. There is a good solution I often use for construction companies: http://www.phprojekt.com. Veryvery complete! :o) -- - PLEASE NO HTML EMAILS! THANX ALOT SAYS MY KMAIL CLIENT. - Best Regards/ Freundliche Grüße Michael Stibane (STIBS) [EMAIL PROTECTED] Training, Linux, Admin, Programming, Web http://www.stibs.cc Escapade Server-Side Scripting Engine Development Team Pensacola - Dallas - Dresden - London http://www.escapade.org +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! -- - PLEASE NO HTML EMAILS! THANX ALOT SAYS MY KMAIL CLIENT. - Best Regards/ Freundliche Grüße Michael Stibane (STIBS) [EMAIL PROTECTED] Training, Linux, Admin, Programming, Web http://www.stibs.cc Escapade Server-Side Scripting Engine Development Team Pensacola - Dallas - Dresden - London http://www.escapade.org +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - 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: content design / database design help
I would take a look at the already available solutions at http://www.hotscripts.com http://php.resourceindex.com (many open source/free) It may be easier to use (and manipulate) one of these to what you need. I am working on a similar project, since I fond many project management solutions to be confused by too many options. part two the syntax you are looking for is UPDATE Status SET projectstatus='whatever' WHERE projectid = 123; http://www.mysql.com/doc/en/UPDATE.html I'd also recommend picking up this small little book. It's a great intro to the power of SQL and will show you how simple some of the cool things you can do are. http://www.amazon.com/exec/obidos/tg/detail/-/0672321289/103-9983226-8456617 I would also recommend putting all project 'properties' in a single table. what I mean is that you don't need a relational setup for items like status and cost where eah project will only have 'one' (Project A costs $5000 and is 40% complete - next week Project A may cost $6000 and be 50% complete - so just update the fields). It would make more sense, and make building your application simpler if these are in a single table. Relations will make sense for items like 'project notes' which would work something like. table 'notes' project_id | create_date | note | author so now we can store a note and know who wrote it and when they did. this same structure can solve your problem of tying contacts to the project table 'project_contacts' project_id | name | company | address | phone | description and whatever other fields you like... olinux --- Randy Hammons [EMAIL PROTECTED] wrote: I'm new and I'm TOTALLY lost! Why am I here? To BEG for help! My office currently keeps track of our projects via an excel spreadsheet. We keep such info as project costing, status, points oif contact, name, description, number, last updated, etc... What I want to do is merge all that info to a mysql database and generate a front end to view / update all applicalbe information via the web with php. Should be easy enough right? Should I be looking at a content management system type of design or should I just be focusing on form manupliation etc? Any suggestions as to how I would design this would be very beneficial. Part two. Lets say I design this puppy using regular static forms. My database design looks (currently and tentatively) like this: table projects columns( projectID(pk) numbername description) table Managers columns ( managerid(pk) managername) table Status columns(projectid(pk)projectstatus) table projectdetails columns(projectid(pk) projectcompletiondate projectcost lastupdate) now lets say someone is viewing a project with projectid (1). If they decide to update the status, we can see that the status tables primary key is projectid. If they decide to update the status could you give me an example of the sql syntax of how that would be updated. We can visually see the key relationship, but what would the syntax look like? We understand that status is related to the project via the projectid, but the database won't replicate that info automatically. How do I make something like that happen? Part three. Any suggestions on how to keep track of the primary and alternate points of contact per project as far as database design is concerned? Thank you for reading this far...look forward to hearing your responses (if any ;)). - 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 __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.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
content design / database design help
I'm new and I'm TOTALLY lost! Why am I here? To BEG for help! My office currently keeps track of our projects via an excel spreadsheet. We keep such info as project costing, status, points oif contact, name, description, number, last updated, etc... What I want to do is merge all that info to a mysql database and generate a front end to view / update all applicalbe information via the web with php. Should be easy enough right? Should I be looking at a content management system type of design or should I just be focusing on form manupliation etc? Any suggestions as to how I would design this would be very beneficial. Part two. Lets say I design this puppy using regular static forms. My database design looks (currently and tentatively) like this: table projects columns( projectID(pk) numbernamedescription) table Managers columns ( managerid(pk) managername) table Status columns(projectid(pk)projectstatus) table projectdetails columns(projectid(pk) projectcompletiondateprojectcost lastupdate) now lets say someone is viewing a project with projectid (1). If they decide to update the status, we can see that the status tables primary key is projectid. If they decide to update the status could you give me an example of the sql syntax of how that would be updated. We can visually see the key relationship, but what would the syntax look like? We understand that status is related to the project via the projectid, but the database won't replicate that info automatically. How do I make something like that happen? Part three. Any suggestions on how to keep track of the primary and alternate points of contact per project as far as database design is concerned? Thank you for reading this far...look forward to hearing your responses (if any ;)). - 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
Database Table Design Help
I am creating site for greeting cards and need help desiging tables for DB Currently I have three tables.. 1) maincat which has following fields: maincatid (autoincrement) main_name (name of category) 2) subcat which has following fields: subcatid (autoincrement) maincatid sub_name (name of sub category) Here's is my problem..each sub categories can also have sub categories (If main category is Birthday and Sub category is His then His can also have sub categories such as Brother,Uncle,Father etc.) Then I need to associates all these categories in cards table...which holds information about each card. To make it more complicated...each card can be displayed in multiple sub categories as well. How should I design all these? Any help will be greatly appreciated! sql, query - 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 help
Hi dn, thanks for replying. I suspect you're right about my 'problem' - I'm probably misunderstanding some simple SQL construct :-) Let's say I have the following tables: parts (partid int, partname varchar(255)) cats(catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: a) Form a query that only returns a list of parts that match a specifc set of categories (i.e. parts_cats has records where catid=1, 3 and 7 for any given partid) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Hope I've explained myself clearly! - thanks in advance, james ps Completely OT question - how do you find your HomeChoice service? Been thinking about getting it for a while. j. At 09:39 10/02/2002 +, you wrote: The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original table relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple entries, but not why. Perhaps most of the 'problem' lies hidden there? =dn - 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 James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - 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
Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - 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
Another question - Re: Quick SQL design help
Hi Amer Thanks for replying - I wonder if you could clear up a point for me. I'm going to have many, many possible multi values - too many for a SET or ENUM column as some other people have suggested. I'm happy using the following tables: parts (partid int, partname varchar(255)) cats(catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: a) Form a query that only returns a list of parts that match a specifc set of categories (e.g. 1=CatA' AND 5='CatE' AND 26='CatZ' only) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Thanks again- james At 22:43 10/02/2002 -0500, Amer Neely wrote: Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). One thing you could do is add a little programming to split the entry into its parts, once retrieved, then run your select on those. But a better solution would be to re-design your table/s to accomodate multiple values. -- Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for shopping carts, data entry forms. We make web sites work! - 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 James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - 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: Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). One thing you could do is add a little programming to split the entry into its parts, once retrieved, then run your select on those. But a better solution would be to re-design your table/s to accomodate multiple values. -- Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for shopping carts, data entry forms. We make web sites work! - 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: Quick SQL design help
Hello James, Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original table relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple entries, but not why. Perhaps most of the 'problem' lies hidden there? =dn - 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
Fwd: Re: Quick SQL design help
Date: Sun, 10 Feb 2002 16:06:55 -0800 To: [EMAIL PROTECTED] From: tj marlin [EMAIL PROTECTED] Subject: Re: Quick SQL design help the SET data type comes to mind. there is a limitation of 64 for the set size. ciao At 05:23 AM 2/10/02 +, you wrote: Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - 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 __ Tom Marlin [EMAIL PROTECTED] fax / voice mail: 714 507 3802 ext 4881 Beware of Programmers who carry screwdrivers. - Leonard Brandwein - 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
Implementation Design Help Required
I've asked this before, but let me describe it another way, and see if anyone has any bright ideas: My client has 3 offices. Each office needs to use same app (over MySQL). Each office needs (effectively) same database. Updates done at each office need to be used at other offices. Updates visibility not too time critical - overnight re-synch is acceptable. Ideal Solution: One central database, all clients at each office connect to it. BUT - geographically separate, telecomms costs prohibit live on-line access at reasonable speed to same database. Should I : a) Use a copy of database at each office. Log each update at each office, and apply these at night to other 2 databases. b) Use replication with link down until night-time, when replication method will do re synch for me. c) Some better solution you wonderful people out there are going to suggest to me, and I will say Do, why didn't I think of that ! a) looks clumsy b) Don't know if replication model is appropriate. TIA David. - 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: Another question - Re: Quick SQL design help
James Carrier wrote: Hi Amer Thanks for replying - I wonder if you could clear up a point for me. I'm going to have many, many possible multi values - too many for a SET or ENUM column as some other people have suggested. I'm happy using the following tables: parts (partid int, partname varchar(255)) cats(catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: So from this are you saying that a given part may have multiple categories? Can a category contain multiple parts? (many to many). That is usually an indicator to split up the data into another table. I suggest you try something like this: 1) add another field to parts (catid int) which reflects cats.catid 2) move the cats.catid to the new column in parts 3) delete parts_cats - it's now redundant a) Form a query that only returns a list of parts that match a specifc set of categories (e.g. 1=CatA' AND 5='CatE' AND 26='CatZ' only) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Here's a dummy db I set up to play with this: mysql show tables; +---+ | Tables_in_carrier | +---+ | cats | | parts | +---+ 2 rows in set (0.00 sec) mysql describe cats; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | cat_id | int(10) unsigned | YES | | NULL| | | cat_name | varchar(255) | YES | | NULL| | +--+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe parts; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | part_id | int(10) unsigned | YES | | NULL| | | part_name | varchar(255) | YES | | NULL| | | cat_id| int(10) unsigned | YES | | NULL| | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql select * from cats; +++ | cat_id | cat_name | +++ |101 | oneohone | |102 | oneohtwo | |103 | oneohthree | |104 | oneohfour | +++ 4 rows in set (0.00 sec) mysql select * from parts order by part_id; +-+---++ | part_id | part_name | cat_id | +-+---++ | 1 | one |101 | | 1 | one |102 | | 2 | two |102 | | 2 | two |104 | | 3 | three |103 | | 3 | three |104 | | 4 | four |103 | | 5 | five |101 | | 6 | six |102 | +-+---++ 9 rows in set (0.00 sec) mysql select * from parts - where cat_id=101 or cat_id=104 - order by part_id; +-+---++ | part_id | part_name | cat_id | +-+---++ | 1 | one |101 | | 2 | two |104 | | 3 | three |104 | | 5 | five |101 | +-+---++ 4 rows in set (0.00 sec) mysql select * from parts - where cat_id=101 or cat_id=102; +-+---++ | part_id | part_name | cat_id | +-+---++ | 1 | one |101 | | 5 | five |101 | | 2 | two |102 | | 6 | six |102 | | 1 | one |102 | +-+---++ 5 rows in set (0.00 sec) mysql select * from parts - where part_id=3; +-+---++ | part_id | part_name | cat_id | +-+---++ | 3 | three |103 | | 3 | three |104 | +-+---++ 2 rows in set (0.00 sec) mysql select * from parts - where part_id=1; +-+---++ | part_id | part_name | cat_id | +-+---++ | 1 | one |101 | | 1 | one |102 | +-+---++ 2 rows in set (0.00 sec) mysql select * from parts - where part_id=4; +-+---++ | part_id | part_name | cat_id | +-+---++ | 4 | four |103 |
Re: Quick SQL design help
Hello James, Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original table relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple entries, but not why. Perhaps most of the 'problem' lies hidden there? =dn - 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
Fwd: Re: Quick SQL design help
Date: Sun, 10 Feb 2002 16:06:55 -0800 To: [EMAIL PROTECTED] From: tj marlin [EMAIL PROTECTED] Subject: Re: Quick SQL design help the SET data type comes to mind. there is a limitation of 64 for the set size. ciao At 05:23 AM 2/10/02 +, you wrote: Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - 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 __ Tom Marlin [EMAIL PROTECTED] fax / voice mail: 714 507 3802 ext 4881 Beware of Programmers who carry screwdrivers. - Leonard Brandwein - 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: Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). One thing you could do is add a little programming to split the entry into its parts, once retrieved, then run your select on those. But a better solution would be to re-design your table/s to accomodate multiple values. -- Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for shopping carts, data entry forms. We make web sites work! - 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
Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - 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
