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 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
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 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
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
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 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
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 > Looking for some help / comments if possible ? > > Cheers > Neil > > -- Forwarded message -- > From: Neil Tompkins > 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
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_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 __ > 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
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_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 __ > 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
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_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" > To: "BobSharp" > Cc: > 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) > &g
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" > To: "BobSharp" > Cc: > 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
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" To: "BobSharp" Cc: 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
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: 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 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]
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]
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" 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 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
- Original Message - From: "Mike Blezien" <[EMAIL PROTECTED]> To: "MySQL List" 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 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
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]>; 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 particul
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]>; 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 |
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]>; 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
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]>; 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&#
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
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:
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
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
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
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
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]
