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
>-Original Message- >From: AndrewJames [mailto:andrewhu...@gmail.com] >Sent: Saturday, September 12, 2009 1:20 AM >To: Kyong Kim; Arthur Fuller >Cc: Claudio Nanni; mysql >Subject: Re: database design > >thank you all, i think > >"You probably wouldn't need Article_Type table if you're going to store >Article_Type value directly." > >is my answer. > [JS] I might have missed part of the discussion, but a foreign key back to an Article_Type table would help enforce data integrity. 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 > >-- >From: "Kyong Kim" >Sent: Saturday, September 12, 2009 8:22 AM >To: "Arthur Fuller" >Cc: "Claudio Nanni" ; "AndrewJames" >; "mysql" >Subject: Re: database design > >> A) You would probably want to populate the Article.Article_Type column >> with Article_Type.ID. You probably wouldn't need Article_Type table if >> you're going to store Article_Type value directly. >> >> I would also consider the use of natural primary key vs surrogate >> primary key. We've seen good results with primary key lookups on large >> tables (especially creating grouped subsets of data) >> >> If you imagine your data set growing fairly large, you should take a >> stab at projecting your workload to determine whether you would want >> to optimize access speed vs insert. >> >> For example, if you will be searching the article table by uid, you >> might want to cluster the data by uid so all related articles will be >> stored next to each other. >> >> Kyong >> >> On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller >> wrote: >>> I agree with Claudio. You have your design correct. The only other thing >>> you >>> need is the uid qualifier. Presumably you are using PHP or some other >>> front >>> end to present your data. Your front end would request the user's name >>> and >>> password, saving the uid in a variable and then issuing the select with a >>> WHERE clause that passes the uid in: >>> select * from articles A left joing article_types AT on A.article_type = >>> AT.Arcticle_types_id WHERE A.uid = >>> >>> hth, >>> Arthur >>> >>> On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni >>> wrote: >>> >>>> A.J., It sounds good to me! >>>> You can be a little confused but you did it well, >>>> It seems you have all you need there. >>>> >>>> A) Yes >>>> B) select * from articles A left join article_types AT on >>>> A.article_type = >>>> AT.article_types_id >>>> >>>> Claudio >>>> >>>> >>>> >>>> >>>> 2009/9/11 AndrewJames >>>> >>>> > This is a bit of a long shot, but i really need some help and or >>>> > directed >>>> > to the best reading resources. >>>> > >>>> > as i begun building my database (as i went along), i now realise i >>>> > have >>>> to >>>> > stop coding and sit back and design the database properly before i can >>>> > go >>>> > on. >>>> > >>>> > However i am still unable to wrap my head around what data to put into >>>> what >>>> > tables, and which columns i need to link to make the relationships. >>>> > so >>>> far, >>>> > here is what i have. >>>> > >>>> > TABLES: >>>> > >>>> > users >>>> > -uid(pk) >>>> > -username >>>> > -password >>>> > >>>> > articles >>>> > -article_id(pk) >>>> > -uid(fk) >>>> > -article_type(fk) >>>> > -article_subject >>>> > -article_body >>>> > >>>> > article_types >>>> > -article_types_id(pk) >>>> > -article_type >>>> > >>>> > So i want the user to be able to login and add articles. >>>> > >>>> > I then want to be able to view all the articles the user has >>>> > submitted. >>>> > >>>> > So in my understanding i need to link the users.uid(pk) to the >>>> > articles.uid(fk) (so i know which user the article belongs to, please >>>> > correct and update me if i am wrong) >>>> > >>>> > I am stuck at this point. >>>> > >>>> > A) Have i created the right tables and columns for each table, AND >>>> > B) How do i link the articles.article_type to articles_type.type? (IF >>>> > in >>>> > fact that is even the correct linkage)?? >>>> > >>>> > -- >>>> > MySQL General Mailing List >>>> > For list archives: http://lists.mysql.com/mysql >>>> > To unsubscribe: >>>> > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com >>>> > >>>> > >>>> >>>> >>>> -- >>>> Claudio >>>> >>> >> > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.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
Well, if you have a fixed number of article types, then maybe. If there is a chance of more types being added later, then no. Are you planning to hard code selection lists in your front end, or would you like to retrieve data from sql ??? Maybe a read up on Database Normalization is due: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html On Sat, September 12, 2009 07:19, AndrewJames wrote: > thank you all, i think > > "You probably wouldn't need Article_Type table if you're going to store > Article_Type value directly." > > is my answer. > > > -- > From: "Kyong Kim" > Sent: Saturday, September 12, 2009 8:22 AM > To: "Arthur Fuller" > Cc: "Claudio Nanni" ; "AndrewJames" > ; "mysql" > Subject: Re: database design > >> A) You would probably want to populate the Article.Article_Type column >> with Article_Type.ID. You probably wouldn't need Article_Type table if >> you're going to store Article_Type value directly. >> >> I would also consider the use of natural primary key vs surrogate >> primary key. We've seen good results with primary key lookups on large >> tables (especially creating grouped subsets of data) >> >> If you imagine your data set growing fairly large, you should take a >> stab at projecting your workload to determine whether you would want >> to optimize access speed vs insert. >> >> For example, if you will be searching the article table by uid, you >> might want to cluster the data by uid so all related articles will be >> stored next to each other. >> >> Kyong >> >> On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller >> wrote: >>> I agree with Claudio. You have your design correct. The only other thing >>> you >>> need is the uid qualifier. Presumably you are using PHP or some other >>> front >>> end to present your data. Your front end would request the user's name >>> and >>> password, saving the uid in a variable and then issuing the select with a >>> WHERE clause that passes the uid in: >>> select * from articles A left joing article_types AT on A.article_type = >>> AT.Arcticle_types_id WHERE A.uid = >>> >>> hth, >>> Arthur >>> >>> On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni >>> wrote: >>> >>>> A.J., It sounds good to me! >>>> You can be a little confused but you did it well, >>>> It seems you have all you need there. >>>> >>>> A) Yes >>>> B) select * from articles A left join article_types AT on >>>> A.article_type = >>>> AT.article_types_id >>>> >>>> Claudio >>>> >>>> >>>> >>>> >>>> 2009/9/11 AndrewJames >>>> >>>> > This is a bit of a long shot, but i really need some help and or >>>> > directed >>>> > to the best reading resources. >>>> > >>>> > as i begun building my database (as i went along), i now realise i >>>> > have >>>> to >>>> > stop coding and sit back and design the database properly before i can >>>> > go >>>> > on. >>>> > >>>> > However i am still unable to wrap my head around what data to put into >>>> what >>>> > tables, and which columns i need to link to make the relationships. >>>> > so >>>> far, >>>> > here is what i have. >>>> > >>>> > TABLES: >>>> > >>>> > users >>>> > -uid(pk) >>>> > -username >>>> > -password >>>> > >>>> > articles >>>> > -article_id(pk) >>>> > -uid(fk) >>>> > -article_type(fk) >>>> > -article_subject >>>> > -article_body >>>> > >>>> > article_types >>>> > -article_types_id(pk) >>>> > -article_type >>>> > >>>> > So i want the user to be able to login and add articles. >>>> > >>>> > I then want to be able to view all the articles the user has >>>> > submitted. >>>> > >>>> > So in my understanding i need to link the users.uid(pk) to the >>>> > articles.uid(fk) (so i know which user the article belongs to, please >>>> > correct and update me if i am wrong) >>>> > >>>> > I am stuck at this point. >>>> > >>>> > A) Have i created the right tables and columns for each table, AND >>>> > B) How do i link the articles.article_type to articles_type.type? (IF >>>> > in >>>> > fact that is even the correct linkage)?? >>>> > >>>> > -- >>>> > MySQL General Mailing List >>>> > For list archives: http://lists.mysql.com/mysql >>>> > To unsubscribe: >>>> > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com >>>> > >>>> > >>>> >>>> >>>> -- >>>> Claudio >>>> >>> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mog...@fumlersoft.dk > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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
Storing it directly will cause problems when you want to add a new Article Type. IMO it's better to have an ArticleTypes table (AutoIncrement) and store its values in the ArticleTypeID column in the Articles table. A. On Sat, Sep 12, 2009 at 1:19 AM, AndrewJames wrote: > thank you all, i think > > "You probably wouldn't need Article_Type table if you're going to store > Article_Type value directly." > > is my answer. > >
R: RE: database design
Manageability. Id(surrogate) can be autoincrement so managed by the engine, Joins are simpler to write. Imagine a natural key composed of three or more fields. Practical, with surrogate you can 'book' a record while not knowing yet all of the values that compose the natural key. Just a couple of points. Claudio Il giorno 12 set, 2009 12:57 m., "Martin Gainty" ha scritto: what are the advantages to implementing a natural key over surrogate key ? 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. > Date: Fri, 11 Sep 2009 15:22:57 -0700 > Subject: Re: database design > From: kykim...@gmail.com > To: fuller.art...@gmail.com > CC: claudio.na...@gmail.com; andrewhu...@gmail.com; mysql@lists.mysql.com > > A) You would probably want to populate the Article.Article_Type column > with Article_Type.ID > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > -- Get back to school stuff for them and cashback for you. Try Bing now.<http://www.bing.com/cashback?form=MSHYCB&publ=WLHMTAG&crea=TEXT_MSHYCB_BackToSchool_Cashback_BTSCashback_1x1>
Re: database design
thank you all, i think "You probably wouldn't need Article_Type table if you're going to store Article_Type value directly." is my answer. -- From: "Kyong Kim" Sent: Saturday, September 12, 2009 8:22 AM To: "Arthur Fuller" Cc: "Claudio Nanni" ; "AndrewJames" ; "mysql" Subject: Re: database design A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller wrote: I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni wrote: A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames > This is a bit of a long shot, but i really need some help and or > directed > to the best reading resources. > > as i begun building my database (as i went along), i now realise i > have to > stop coding and sit back and design the database properly before i can > go > on. > > However i am still unable to wrap my head around what data to put into what > tables, and which columns i need to link to make the relationships. > so far, > here is what i have. > > TABLES: > > users > -uid(pk) > -username > -password > > articles > -article_id(pk) > -uid(fk) > -article_type(fk) > -article_subject > -article_body > > article_types > -article_types_id(pk) > -article_type > > So i want the user to be able to login and add articles. > > I then want to be able to view all the articles the user has > submitted. > > So in my understanding i need to link the users.uid(pk) to the > articles.uid(fk) (so i know which user the article belongs to, please > correct and update me if i am wrong) > > I am stuck at this point. > > A) Have i created the right tables and columns for each table, AND > B) How do i link the articles.article_type to articles_type.type? (IF > in > fact that is even the correct linkage)?? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- Claudio -- 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
A) You would probably want to populate the Article.Article_Type column with Article_Type.ID. You probably wouldn't need Article_Type table if you're going to store Article_Type value directly. I would also consider the use of natural primary key vs surrogate primary key. We've seen good results with primary key lookups on large tables (especially creating grouped subsets of data) If you imagine your data set growing fairly large, you should take a stab at projecting your workload to determine whether you would want to optimize access speed vs insert. For example, if you will be searching the article table by uid, you might want to cluster the data by uid so all related articles will be stored next to each other. Kyong On Fri, Sep 11, 2009 at 5:44 AM, Arthur Fuller wrote: > I agree with Claudio. You have your design correct. The only other thing you > need is the uid qualifier. Presumably you are using PHP or some other front > end to present your data. Your front end would request the user's name and > password, saving the uid in a variable and then issuing the select with a > WHERE clause that passes the uid in: > select * from articles A left joing article_types AT on A.article_type = > AT.Arcticle_types_id WHERE A.uid = > > hth, > Arthur > > On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni wrote: > >> A.J., It sounds good to me! >> You can be a little confused but you did it well, >> It seems you have all you need there. >> >> A) Yes >> B) select * from articles A left join article_types AT on A.article_type = >> AT.article_types_id >> >> Claudio >> >> >> >> >> 2009/9/11 AndrewJames >> >> > This is a bit of a long shot, but i really need some help and or directed >> > to the best reading resources. >> > >> > as i begun building my database (as i went along), i now realise i have >> to >> > stop coding and sit back and design the database properly before i can go >> > on. >> > >> > However i am still unable to wrap my head around what data to put into >> what >> > tables, and which columns i need to link to make the relationships. so >> far, >> > here is what i have. >> > >> > TABLES: >> > >> > users >> > -uid(pk) >> > -username >> > -password >> > >> > articles >> > -article_id(pk) >> > -uid(fk) >> > -article_type(fk) >> > -article_subject >> > -article_body >> > >> > article_types >> > -article_types_id(pk) >> > -article_type >> > >> > So i want the user to be able to login and add articles. >> > >> > I then want to be able to view all the articles the user has submitted. >> > >> > So in my understanding i need to link the users.uid(pk) to the >> > articles.uid(fk) (so i know which user the article belongs to, please >> > correct and update me if i am wrong) >> > >> > I am stuck at this point. >> > >> > A) Have i created the right tables and columns for each table, AND >> > B) How do i link the articles.article_type to articles_type.type? (IF in >> > fact that is even the correct linkage)?? >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: >> > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com >> > >> > >> >> >> -- >> Claudio >> > -- 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
I agree with Claudio. You have your design correct. The only other thing you need is the uid qualifier. Presumably you are using PHP or some other front end to present your data. Your front end would request the user's name and password, saving the uid in a variable and then issuing the select with a WHERE clause that passes the uid in: select * from articles A left joing article_types AT on A.article_type = AT.Arcticle_types_id WHERE A.uid = hth, Arthur On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni wrote: > A.J., It sounds good to me! > You can be a little confused but you did it well, > It seems you have all you need there. > > A) Yes > B) select * from articles A left join article_types AT on A.article_type = > AT.article_types_id > > Claudio > > > > > 2009/9/11 AndrewJames > > > This is a bit of a long shot, but i really need some help and or directed > > to the best reading resources. > > > > as i begun building my database (as i went along), i now realise i have > to > > stop coding and sit back and design the database properly before i can go > > on. > > > > However i am still unable to wrap my head around what data to put into > what > > tables, and which columns i need to link to make the relationships. so > far, > > here is what i have. > > > > TABLES: > > > > users > > -uid(pk) > > -username > > -password > > > > articles > > -article_id(pk) > > -uid(fk) > > -article_type(fk) > > -article_subject > > -article_body > > > > article_types > > -article_types_id(pk) > > -article_type > > > > So i want the user to be able to login and add articles. > > > > I then want to be able to view all the articles the user has submitted. > > > > So in my understanding i need to link the users.uid(pk) to the > > articles.uid(fk) (so i know which user the article belongs to, please > > correct and update me if i am wrong) > > > > I am stuck at this point. > > > > A) Have i created the right tables and columns for each table, AND > > B) How do i link the articles.article_type to articles_type.type? (IF in > > fact that is even the correct linkage)?? > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > > > > > > -- > Claudio >
Re: database design
A.J., It sounds good to me! You can be a little confused but you did it well, It seems you have all you need there. A) Yes B) select * from articles A left join article_types AT on A.article_type = AT.article_types_id Claudio 2009/9/11 AndrewJames > This is a bit of a long shot, but i really need some help and or directed > to the best reading resources. > > as i begun building my database (as i went along), i now realise i have to > stop coding and sit back and design the database properly before i can go > on. > > However i am still unable to wrap my head around what data to put into what > tables, and which columns i need to link to make the relationships. so far, > here is what i have. > > TABLES: > > users > -uid(pk) > -username > -password > > articles > -article_id(pk) > -uid(fk) > -article_type(fk) > -article_subject > -article_body > > article_types > -article_types_id(pk) > -article_type > > So i want the user to be able to login and add articles. > > I then want to be able to view all the articles the user has submitted. > > So in my understanding i need to link the users.uid(pk) to the > articles.uid(fk) (so i know which user the article belongs to, please > correct and update me if i am wrong) > > I am stuck at this point. > > A) Have i created the right tables and columns for each table, AND > B) How do i link the articles.article_type to articles_type.type? (IF in > fact that is even the correct linkage)?? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > > -- 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
T NULL, `colour_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`colour_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_description` ( `garment_id` smallint(5) unsigned NOT NULL, `desc_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`desc_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_size` ( `garment_id` smallint(5) unsigned NOT NULL, `size_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`size_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_to_title` ( `garment_id` smallint(5) unsigned NOT NULL, `title_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`garment_id`,`title_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `garment_type` ( `type_id` smallint(5) unsigned NOT NULL auto_increment, `supplier_id` smallint(5) unsigned NOT NULL, `type` varchar(30) NOT NULL, PRIMARY KEY (`type_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `sizes` ( `size_id` smallint(5) unsigned NOT NULL auto_increment, `supplier_id` smallint(5) unsigned NOT NULL, `type_id` smallint(5) unsigned NOT NULL, `size` varchar(15) NOT NULL, PRIMARY KEY (`size_id`), UNIQUE KEY `size` (`size`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `suppliers` ( `supplier_id` smallint(5) unsigned NOT NULL auto_increment, `supplier` varchar(30) NOT NULL, PRIMARY KEY (`supplier_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `title` ( `title_id` smallint(5) unsigned NOT NULL auto_increment, `title` varchar(60) NOT NULL, PRIMARY KEY (`title_id`), UNIQUE KEY `title` (`title`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Thanks for any continued support. > From: John Meyer <[EMAIL PROTECTED]> > Date: Wed, 23 May 2007 07:28:23 -0600 > To: > Subject: Re: Database design > > Officelink wrote: >> Hi everyone, >> >> I¹m trying to set up a database with information that will be used in a >> garment slideshow in flash. >> >> The information to be included as part of the slideshow would be: >> code, optional title, description, colours, sizes, garment image, fabric >> swatch image >> >> Each clothing item to be included in the slideshow will belong to one of >> eleven or so categories. Each of the categories will belong to one of two >> category types. >> >> I also planned to set up a simple CMS that would allow the information to be >> added, edited and deleted from the database. >> >> With the above goals in mind, I came up with two tables as follows: >> >> GARMENTS TABLE >> garment_id, int(11), not null, auto_increment, primary key >> cat_id, int(10), unsigned, not null, default 0 >> garment_code, varchar(30), not null >> garment_title, varchar(40), null >> garment_desc, varchar(255), not null >> garment_image, varchar(50), not null >> garment_colour, varchar(50), not null >> garment_swatch, varchar(50), null >> garment_sizes, varchar(100), not null >> >> CATEGORIES TABLE >> cat_id, int(10), not null, auto_increment, primary key >> cat_name, varchar(40), not null >> cat_type, tinyint(4), not null, default 1 >> >> I was worried about repeating data in some of the columns, for example the >> garment_desc column would have information about sleeve length, cuff type, >> fabric, fabric composition etc. and I thought that all these areas could >> possibly be broken up into separate tables, but I wasn¹t sure if it was >> necessary. Also the colour and size columns would have a lot of repetitive >> data. >> > > While normalization does have the goal of eliminating repetition, there > are other reasons. Most notably, you don't want to introduce errors or > even differences into your database. > A person who accidentally types "eRd", for instance. You might, and I > emphasize the word "might", consider breaking color and size into two > different tables based upon the following: > 1. The possible set of "valid" answers. > 2. Whether that element will be used in any sort of grouping or > searching level (are you able to search by color, for instance) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
Normalization is about using ids to minimize change, which also eliminates repetition. It's fine to have the color "red" repeated throughout your table as long as it will never change. But if you suddenly have two shades of red, you'll need to update all the records that say "red". If you used id's, you just update the text associated with the id, a single record. Nobody ever designs to 5th normal form (except as an exercise), you usually reach level 2 or 3. When designing a database, you want to determine the various "objects" you need to hold and their attributes. One mistake is that you are putting the garment attributes in the a general description field. Which is fine if you don't need to search on more than free form text. Sleeve, fabric, cuff, colors, etc. are all attributes of the garment. Since a garment can have multiple attributes, the attributes should be in a separate table. I would create an attributes table that contains all the attributes of the garment. Then you would be able to search the single table to find all garments made of a certain fabric with a certain cuff type. The garment attributes table contains codes that link to a description. The description could have multiple fields so you can handle conversions between different markets. For example, sizes vary between different geographic areas (my sneaker has sizes on the label for US, UK, EU and CM). Since the size is represented by an ID, you can search on any of the sizes the ID represents. - Original Message - From: "Officelink" <[EMAIL PROTECTED]> To: Sent: Wednesday, May 23, 2007 8:29 AM Subject: Database design Hi everyone, I¹m trying to set up a database with information that will be used in a garment slideshow in flash. The information to be included as part of the slideshow would be: code, optional title, description, colours, sizes, garment image, fabric swatch image Each clothing item to be included in the slideshow will belong to one of eleven or so categories. Each of the categories will belong to one of two category types. I also planned to set up a simple CMS that would allow the information to be added, edited and deleted from the database. With the above goals in mind, I came up with two tables as follows: GARMENTS TABLE garment_id, int(11), not null, auto_increment, primary key cat_id, int(10), unsigned, not null, default 0 garment_code, varchar(30), not null garment_title, varchar(40), null garment_desc, varchar(255), not null garment_image, varchar(50), not null garment_colour, varchar(50), not null garment_swatch, varchar(50), null garment_sizes, varchar(100), not null CATEGORIES TABLE cat_id, int(10), not null, auto_increment, primary key cat_name, varchar(40), not null cat_type, tinyint(4), not null, default 1 I was worried about repeating data in some of the columns, for example the garment_desc column would have information about sleeve length, cuff type, fabric, fabric composition etc. and I thought that all these areas could possibly be broken up into separate tables, but I wasn¹t sure if it was necessary. Also the colour and size columns would have a lot of repetitive data. Someone indicated that normalization is not about eliminating repetition, it¹s about ensuring that the non-key attributes are functionally dependent on the entire primary key, but then I read somewhere that you¹re supposed to break down the information as far as possible to avoid redundancy so I¹m a bit confused. Or does it depend on the situation and what¹s required of the database. I mean say the CMS needed to have more functionality than what I indicated above I mean say the client wanted to be able to generate reports based on style information such as fabric composition or sleeve style etc. - would this change the setup? I wondered if someone could comment on the setup to see if I¹m on the right track here? Appreciate any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
Officelink wrote: > Hi everyone, > > I¹m trying to set up a database with information that will be used in a > garment slideshow in flash. > > The information to be included as part of the slideshow would be: > code, optional title, description, colours, sizes, garment image, fabric > swatch image > > Each clothing item to be included in the slideshow will belong to one of > eleven or so categories. Each of the categories will belong to one of two > category types. > > I also planned to set up a simple CMS that would allow the information to be > added, edited and deleted from the database. > > With the above goals in mind, I came up with two tables as follows: > > GARMENTS TABLE > garment_id, int(11), not null, auto_increment, primary key > cat_id, int(10), unsigned, not null, default 0 > garment_code, varchar(30), not null > garment_title, varchar(40), null > garment_desc, varchar(255), not null > garment_image, varchar(50), not null > garment_colour, varchar(50), not null > garment_swatch, varchar(50), null > garment_sizes, varchar(100), not null > > CATEGORIES TABLE > cat_id, int(10), not null, auto_increment, primary key > cat_name, varchar(40), not null > cat_type, tinyint(4), not null, default 1 > > I was worried about repeating data in some of the columns, for example the > garment_desc column would have information about sleeve length, cuff type, > fabric, fabric composition etc. and I thought that all these areas could > possibly be broken up into separate tables, but I wasn¹t sure if it was > necessary. Also the colour and size columns would have a lot of repetitive > data. > While normalization does have the goal of eliminating repetition, there are other reasons. Most notably, you don't want to introduce errors or even differences into your database. A person who accidentally types "eRd", for instance. You might, and I emphasize the word "might", consider breaking color and size into two different tables based upon the following: 1. The possible set of "valid" answers. 2. Whether that element will be used in any sort of grouping or searching level (are you able to search by color, for instance) -- The NCP Revue -- http://www.ncprevue.com/blog -- 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 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 question
On 8/7/06, James Tu <[EMAIL PROTECTED]> wrote: If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? Hi James, There are really two elements to this problem. The first element is how quickly MySQL can extract the messages you want from a large table. This requires that you know in advance the type of queries you're going to do (all messages by one user? all messages in a certain time window?) and be sure that these queries are approximately O(log N) rather than O(N) or worse. You will need to change your database design to fit the queries that you'll be doing. O(log N) queries would generally be characterized by the fields you're searching or sorting on being key fields (i.e. MySQL makes an index or BTREE or whatever it makes rather than having to go through the entire table linearly). The second element is data presentation. In developing web applications at least, if the first data on a page is displayed by the browser while the rest of the data is loading, the user perceives the load as being faster than it really is because the user is looking at the first data while the rest is loading. So, to make things more "snappy", you might do more than one query to avoid large result sets. Dave.
Re: Database design question
Wow, I didn't know that can happen. I'll definitely take that into consideration. Thanks Brent. On Aug 7, 2006, at 4:26 PM, Brent Baisley wrote: If you're looking to be put at ease, I've got a table with 250+ million records, but I've heard of people with larger tables than that on this list. You might want to also looking into using a compound primary key, meaning userid+messageid. Something like this: CREATE TABLE `message` ( `userid` int unsigned NOT NULL default '', `messageid` int unsigned NOT NULL auto_increment, `message` text, ... PRIMARY KEY (`userid`,`messageid`) ) What that does is give each user their own incrementing message id. Then you can do things like allow users to enter a message id directly with a number that would be easy for them to remember. Just an idea. - Original Message - From: "James Tu" <[EMAIL PROTECTED]> To: Sent: Monday, August 07, 2006 4:11 PM Subject: Re: Database design question Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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 question
Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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 question
One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- 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 question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. One table, with a user_id field. If you're worried about searching through millions of records, perhaps you could have archival tables that don't normally get searched and move messages from one to other after they get "old"... -p -- 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]
Re: database design
Matthew Lenz wrote: anyone using openoffice:base to design mysql db's? back when I tried it earlier this year it wasn't able to define relationships which made it pretty much useless as a time saving tool. Hi Matt, Although it's slightly OT here, there is still a lot of development going on in Base. The most recent version I downloaded is a "version 2 beta" with internal version 1.9.130. It has lot's of improvements over previous releases, but it's still not the final release version. The best thing you can do is try it with a recent build (the 1.x series also had a recent update to 1.1.5 BTW) and submit an issue in the bug tracking system on the site. If you include version numbers of your OS, MySQL, etc. and detailed instructions on how to duplicate the problems you encountered you can help the development a bit further. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design query
I think I have found the solution for my problem. I made the following changes: -> I added a new field RecordID in GroupMemberInfo to make the records unique -> Instead of MemberID and GroupID, I'm now using MemberName and GroupName. I made this change since in Active Directory every name is unique. -> What I found out that in mysql, a FK field can refer to any index field in parent table and not necessarily only Primary Key field. -> So instead of making MemberID and GroupID as primarykey, RecordID is primary key now and MemberID is just an index. I don't know if it's a bug in Mysql or it's an added feature that a FK field can refer to any index field in parent table. CREATE TABLE `groupinfo` ( `GroupID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberCount` int(11) default NULL, PRIMARY KEY (`GroupID`), UNIQUE KEY `i_GroupName` TYPE BTREE (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `groupmemberinfo` ( `RecordID` bigint(20) NOT NULL auto_increment, `GroupName` varchar(128) NOT NULL default '', `MemberName` varchar(128) NOT NULL default '', `MemberType` tinyint(4) NOT NULL default '0', PRIMARY KEY (`RecordID`), KEY `i_MemberName` TYPE HASH (`MemberName`), CONSTRAINT `FK_groupmemberinfo_GroupName` FOREIGN KEY (`GroupName`) REFERENCES `groupinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `hostinfo` ( `HostID` bigint(20) NOT NULL auto_increment, `HostName` varchar(128) NOT NULL default '', `Password` tinyblob NOT NULL default '', PRIMARY KEY (`HostID`), KEY `i_HostName` (`HostName`), CONSTRAINT `FK_hostinfo_HostName` FOREIGN KEY (`HostName`) REFERENCES `groupmemberinfo` (`MemberName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `userinfo` ( `UserID` bigint(20) NOT NULL default '0', `UserName` varchar(128) default NULL, `Password` tinyblob, PRIMARY KEY (`UserID`), KEY `i_UserName` (`UserName`), CONSTRAINT `FK_userinfo_UserName` FOREIGN KEY (`UserName`) REFERENCES `groupmemberinfo` (`GroupName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I know for sure that this is not the best solution. But now both User and Host are referring to GroupMemberInfo. So I have constraints at DB level Thanks to all who replied, Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 01, 2005 8:39 AM To: 'rtroiana' Subject: RE: Database design query I know what you are trying to do and I can see the logic advantage of having a single table that defines the the group relationship for users hosts and groups. I just don't think the rules governing foreign keys will allow this. Your original thought of enforcing the relationships i.e. cascade delete/update etc. at the application vs the database is the only way I can see to make this happen. Otherwise you are back to three tables with the added code to find all members of a group across the 3 tables. I don't know that I've added much, but I enjoyed the dialog. -----Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 4:23 PM To: 'Gordon' Subject: RE: Database design query I can have three different foreign key definitions on one field, but it expects the same value in all the three parent tables. So that's not the right way to implement it What I'm trying to do is: Member id as primary key and UserID, HostID and groupID as foreign keys But since in GroupMember table a member can be in more than 1 group, so I have combination of MemberID, GroupId and MemberType as primary key If I use these 3 as primary key, so UserID, HostID and groupID can't refer MemberID as primary key. That's what the problem is. ++Reema -Original Message- From: Gordon [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 5:14 PM To: 'rtroiana'; mysql@lists.mysql.com Subject: RE: Database design query IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table. Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations betw
RE: Database design query
IF GroupID, HostID and UserID are unique between the three sets then your GroupMember table will work although I would still be tempted to add a MemberType in the GroupMember table. Isn't MemberID the Foreign Key to UserID/HostID/GroupID althugh I don't know if you can have three different foreign key definitions on one field. If not I think you are stuck with 3 tables instead of trying to do it in one. -Original Message- From: rtroiana [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 31, 2005 12:23 PM To: mysql@lists.mysql.com Subject: Database design query Hi All, I'm trying to get data from Active Directory and storing in database. So I have the following tables with their corresponding primary keys: Group (GroupID) Host (HostID) User (UserID) GroupMember(GroupID, MemberID) The relations between them according to Active Directory should be as follows: 1) Host and user can be in one or more groups 2) Groups can be in one or more groups I was trying to implement these relations through Db constraints. I wanted HostID and UserID should refer to MemberID as Primary Key. My problems is a foreign key field cannot refer to a part of primary key, so 1) I should add GroupID in Host and User table, which will be redundancy of data, or 2) Instead of adding a new field, I should not have any relations in the database and just implement it in code. I have tried with three different designs, but all of them have some issues. I tied to add a new table just for Member that would store unique memberID. Does it seem like an overhead? I don't if I can just do with existing table or not. I read some articles online, some of them say it's good to implement relations from DB and some say to reduce overhead, relations can be implemented from code. What would the best database practice that you would suggest? I'll appreciate any help Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design query
Hi, i think you must normalize your table to more than one table. Users/Groups : N:1 Groups/Groups : N:1 Table Users : User_id Host Group_id Table Groups : Group_id Group_parent_id<- is a another group_id No data redondancy and robust implementation. see for example /etc/passwd and /etc/group on a *nix machine. Mathias Selon rtroiana <[EMAIL PROTECTED]>: > Hi All, > > > > I'm trying to get data from Active Directory and storing in database. So I > have the following tables with their corresponding primary keys: > > > > Group (GroupID) > > Host (HostID) > > User (UserID) > > GroupMember(GroupID, MemberID) > > > > The relations between them according to Active Directory should be as > follows: > > > > 1) Host and user can be in one or more groups > > 2) Groups can be in one or more groups > > > > I was trying to implement these relations through Db constraints. I wanted > HostID and UserID should refer to MemberID as Primary Key. > > > > My problems is a foreign key field cannot refer to a part of primary key, so > > 1) I should add GroupID in Host and User table, which will be > redundancy of data, or > > 2) Instead of adding a new field, I should not have any relations in > the database and just implement it in code. > > > > I have tried with three different designs, but all of them have some issues. > I tied to add a new table just for Member that would store unique memberID. > Does it seem like an overhead? I don't if I can just do with existing table > or not. > > > > I read some articles online, some of them say it's good to implement > relations from DB and some say to reduce overhead, relations can be > implemented from code. What would the best database practice that you would > suggest? > > > > I'll appreciate any help > > > > Thanks, > > Reema Duggal Troiana > Senior Software Developer > BitArmor Systems, Inc. > 357 North Craig Street > Ground Floor > Pittsburgh, PA 15213 > [TEL] 412-682-2200 Ext 314 > [FAX] 412-682-2201 > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
I haven't created real project tables yet. But here are the test ones that I'm experimenting with. CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ; CREATE TABLE north ( north_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, north_1 varchar(255) default NULL, north_2 varchar(255) default NULL, north_3 varchar(255) default NULL, PRIMARY KEY (north_id) ) ; CREATE TABLE south ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, south_1 varchar(255) default NULL, south_2 varchar(255) default NULL, south_3 varchar(255) default NULL, timestamp timestamp(14) NOT NULL, PRIMARY KEY (id) ) ; CREATE TABLE west ( west_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, west_1 varchar(255) default NULL, PRIMARY KEY (west_id) ); I want to search on the keywords in all of these tables and retrieve the records from each table that fits the WHERE clause. The question is...should I just: (1) Make four queries and programmatically keep track of the results from each table? ...or (2) Create another table (let's call it `keywords`) and pull out the keywords into this new table...and store an ID that exists in north,south, east, west...and also store a column that tells us which table this ID is from?...Then we do a query on this table? I guess either way I would have to programmatically at some point fetch with four queries... At 2:46 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just "shooting in the dark". Shawn Green Database Administrator Unimin Corporation - Spruce Pine James <[EMAIL PROTECTED]> wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: >james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: > >> I have four different activities. Each has its own set of data that >> I want to save. So, I made four different tables to hold the saved >> data. Each record also has 'keywords' field (essentially this is the >> only field that all tables have in common.) >> >> Later on, I want to search all the keywords in these tables...and >> then retrieve the saved information from the four different tables. >> >> Question: >> Should I just search each of the tables individually? >> >> >> Or should I create another table that will hold the keywords, the >> tablename, and the ID of the saved record in that particular >> table...and then perform my search on this NEW table? >> >> Thanks. >> -- >> -James >> > > > >I would properly index each table and UNION the results of the 4 >searches. Have you considered creating a Full Text index for your >keyword fields? > >Say your 4 tables are called: running, swimming, jumping, and walking > >SELECT 'running', >FROM running >WHERE >UNION >SELECT 'swimming', >FROM swimming >WHERE >UNION >SELECT 'jumping', >FROM jumping >WHERE >UNION >SELECT 'walking', >FROM walking >WHERE ; > >I used the first column only to identify which table each match >comes from. That way if you have records in each table with matching >PK values, you know which table to go back to in order to get any >additional information. The only problem with this type of search is > >that your columns must be compatible between each of >the tables. If the second column is numeric in your first query then >the second column will be coerced to numeric for each of the >remaining 3 queries. If for some reason that fails, then the whole >UNION fails and you get an error. > >Shawn Green >Database Administrator >Unimin Corporation - Spruce Pine > -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) --- -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just "shooting in the dark". Shawn Green Database Administrator Unimin Corporation - Spruce Pine James <[EMAIL PROTECTED]> wrote on 04/26/2005 02:15:49 PM: > I tried that and maybe I'm doing something wrong but... > > -I have to select the same number of columns...for each UNION > -And each of the records from the union fall under the same column > headings as the first SELECT... > > I even tried to define column aliases.. > SELECT `running` as `running_blah`... > > -James > > > > At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: > >james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: > > > >> I have four different activities. Each has its own set of data that > >> I want to save. So, I made four different tables to hold the saved > >> data. Each record also has 'keywords' field (essentially this is the > >> only field that all tables have in common.) > >> > >> Later on, I want to search all the keywords in these tables...and > >> then retrieve the saved information from the four different tables. > >> > >> Question: > >> Should I just search each of the tables individually? > >> > >> > >> Or should I create another table that will hold the keywords, the > >> tablename, and the ID of the saved record in that particular > >> table...and then perform my search on this NEW table? > >> > >> Thanks. > >> -- > >> -James > >> > > > > > >I would properly index each table and UNION the results of the 4 > >searches. Have you considered creating a Full Text index for your > >keyword fields? > > > >Say your 4 tables are called: running, swimming, jumping, and walking > > > >SELECT 'running', > >FROM running > >WHERE > >UNION > >SELECT 'swimming', > >FROM swimming > >WHERE > >UNION > >SELECT 'jumping', > >FROM jumping > >WHERE > >UNION > >SELECT 'walking', > >FROM walking > >WHERE ; > > > >I used the first column only to identify which table each match > >comes from. That way if you have records in each table with matching > >PK values, you know which table to go back to in order to get any > >additional information. The only problem with this type of search is > >that your columns must be compatible between each of > >the tables. If the second column is numeric in your first query then > >the second column will be coerced to numeric for each of the > >remaining 3 queries. If for some reason that fails, then the whole > >UNION fails and you get an error. > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > -- > -James Tu > --- > ESI Design > 111 Fifth Avenue 12th floor > New York, NY 10003 > (212) 989-3993 ext. 357 > (212) 673-4061 (fax) > ---
Re: database design question
I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', FROM running WHERE UNION SELECT 'swimming', FROM swimming WHERE UNION SELECT 'jumping', FROM jumping WHERE UNION SELECT 'walking', FROM walking WHERE ; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: database design question
james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: > I have four different activities. Each has its own set of data that > I want to save. So, I made four different tables to hold the saved > data. Each record also has 'keywords' field (essentially this is the > only field that all tables have in common.) > > Later on, I want to search all the keywords in these tables...and > then retrieve the saved information from the four different tables. > > Question: > Should I just search each of the tables individually? > > > Or should I create another table that will hold the keywords, the > tablename, and the ID of the saved record in that particular > table...and then perform my search on this NEW table? > > Thanks. > -- > -James > I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', FROM running WHERE UNION SELECT 'swimming', FROM swimming WHERE UNION SELECT 'jumping', FROM jumping WHERE UNION SELECT 'walking', FROM walking WHERE ; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Database design question
Mahmoud, >Are these values atomical? >My other question is what are the repercussions of >not putting a table in 2nd and 3rd Normal Form. Your 'choice1-subchoice1' etc are combined values, so they aren't atomic. From your three example dropdown values, it looks as if 'choice' and 'subchoice' have a many-to-many relationship, and if that's so, you'll probably need a second table for 'subchoice' and a third table to store combinations of choice and subschoice. There are examples of disadvantages of not putting a table in 2NF or 3NF at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf, click on 'Normalisation and the normal forms', & read the sections on 1NF, 2NF and 3NF. PB - Mahmoud Badreddine wrote: Hello, I have two questions: I would like to know whether I am violating the principle of atomicity in doing the following: I have a form which has a field with the following options: Choice1, choice2 and choice3. Each of the above choices have two further subchoices : subChoice1, subchoice2. so in my form I have a pull-down list and these are the choices I have: choice1-subchoice1 choice1-subchoice2 choice2-subchoice1 and so on. Are these values atomical? My other question is what are the repercussions of not putting a table in 2nd and 3rd Normal Form. Thank you. No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.10 - Release Date: 4/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design questions
Big post = long response. Thank you for your patience. When it comes to DB design I like to first identify the basic objects: List owners (the people who manage a group), subscribers (the people in the group), and the groups themselves. I think you equate a list owner to a "User" of your service: CREATE TABLE user ( ID int auto_increment not null , FullName varchar(25) not null , Email varchar(255) not null , Password varchar(20) not null . additional fields for business purposes , KEY (ID) , PRIMARY KEY (...) ) For your Primary key I would pick either Email or if you have a different Login field, use that. You have a "subscriber", this is someone on one of the mailing lists. This person may or may not be one of your users. Those are separate identities. CREATE TABLE subscriber ( ID int auto_increment not null , EmailAlias varchar(255) not null , EmailAddress varchar(255) not null , DoNotMail tinyint not null default 0 ... other fields as needed , KEY(ID) , PRIMARY KEY (EmailAddress) ) The "alias" portion of your email address is "Scott Haneda". The address portion is "[EMAIL PROTECTED]". The primary key prevents the same address from being listed more than once. If you want each user to have their own independent subscriber list you would need a table like: CREATE TABLE subscriber ( ID int auto_increment not null , user_ID int not null , EmailAlias varchar(255) not null , EmailAddress varchar(255) not null , DoNotMail tinyint not null default=0 ... other fields as needed , KEY(ID) , KEY(user_Id) , PRIMARY KEY (EmailAddress, user_ID) ) This would let multiple users choose to include someone in one of their lists independently of any other user's list. You have a "group": CREATE TABLE group ( ID int auto_increment not null , user_ID int not null , Name varchar(32) not null , KEY(ID) , KEY(user_ID, ID) , PRIMARY KEY (Name, user_ID) ) The compound key (user_ID, ID) will let the engine collect a list of all of the IDs for a user without actually reading the table,it will get the data directly from the index. The primary key prevents any user from creating the two groups with the same names but allows another user to re-use that name. When a subscriber is added to a group, that is a "subscription". They are subscribing to a mailing list: CREATE TABLE subscription ( subscriber_ID int not null , group_ID int not null , Unsubscribe tinyint not null default 0 , KEY (subscriber_Id, group_ID, unsubscribe) , KEY (group_ID, subscriber_Id, unsubscribe) , PRIMARY KEY(group_ID, subscriber_ID) ) The primary key will prevent the same subscriber from ending up in the same group more than once. The other compound keys (subscriber_Id, group_ID, unsubscribe) and (group_ID, subscriber_Id, unsubscribe) are designed to answer the two most critical questions you have: a) list all of the active/inactive subscribers for a group b) list all of the groups in which a subscriber is active/inactive Yes, it duplicates your data storage but since this is the "central" table to your entire application, I firmly believe that the performance boost will far outweigh the need for extra disk space. Now, you asked how to bulk insert subscribers (based on uploaded lists) so that you preserve the "Unsubscribe" and "DoNotMail" flags. It seems that a simple INSERT IGNORE to the correct table will take care of that. I assume that you preload your lists in to a temporary table. INSERT IGNORE subscriber (EmailAlias, EmailAddress, ... other business fields ...) SELECT Name, Address, ... other business values ... FROM tmpTableNameHere The primary key prevents you from adding the same address twice so you will not change the DoNotMail value if one already exists. Adding subscribers to groups in bulk means you also need check the DoNotMail value so that people who don't want mail, won't get any. Assuming the same temporary table and that the subscribers are being added to group 9. INSERT IGNORE subscription (subscriber_ID, group_ID, Unsubscribe) SELECT s.ID, 9, s.DoNotMail FROM subscriber s INNER JOIN tmpTableNameHere tmp ON tmp.Email = s.EmailAddress This adds "blocked" subscribers to a group but pre-sets those that do not want mail as "Unsubscribed". The primary key will prevent overwriting any subscriber/group combination that already exists. When anyone wants to change their status to DoNotMail, you will need to do only 1 update: UPDATE subscriber s INNER JOIN subscription sub ON s.id = sub.subscriber_ID SET s.DoNotMail =1, sub.Unsubscribe = 1 WHERE EmailAddress='[EMAIL PROTECTED]' Both flags are now set. Does this help at all? I think you were confusing your users with your list
Re: Database design.. Asking again
* Brian Reichert [...] > I don't know if there's a limit at to how many tables can be handled > by a single MySQL query. This limit is actually hardware architecture dependant: You can join 31 tables on a 32-bit server and 63 tables on a 64-bit server. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
On Mon, May 10, 2004 at 09:49:31PM -0700, Scott Haneda wrote: > Sorry for the post again, I hijacked a thread and wanted to get this on the > correct track. > > I can not seem to find the section in the manual that talks about the max > number of tables MySql can use, can someone point me please? A classic hit is how efficient is your filesystem at handling large directories. Each table corresponds to a few files. Too many files in a single subdirectory can cause performance problems. I don't know if there's a limit at to how many tables can be handled by a single MySQL query. There are MySQL system variables that limit as to how big that query command can be. > - > Scott HanedaTel: 415.898.2602 > http://www.newgeo.com Fax: 313.557.5052 > [EMAIL PROTECTED]Novato, CA U.S.A. -- Brian Reichert <[EMAIL PROTECTED]> 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
I'm not sure what the maximum number of tables are per database (512? 1024?), but I wouldn't split your data unless you really have to. 70 million records may seem like a lot for a table, but it's really not. Especially if the physical size of your data is small. Tables with billions of records is not unusual for MySQL. The physical size of the table is limited by the operating system you are running it on, which may mean 4GB. MySQL itself can handle terabyte sized tables. You can also look into using InnoDB as your table type, which would allow you to split your database into separate files without the need to change any of your programming to reference the data. On May 10, 2004, at 7:30 PM, Scott Haneda wrote: I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
Scott Haneda wrote: I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. Another thing you may want to also consider when using huge databases (10M+); is the amount of time for searching and the overhead that would also cause. A with any search, regardless of the size of the database, it will take a small amount of time. Sure that amount of time would be around a couple seconds if even that. While a 10M record database could take significantly more. Which most likely that amount, may be even closer to about a minute or more. Now I don't know if a delay for searching the database would be a critical thing or not for you. Now of course, these time amounts won't be the same for every machine; nor are these time amounts be exact. Now if your are not in a time critical area, then you most likely won't care about this. Though, you may want to just do a simple query through a table, on your worst case environment; which from what you say may be up to about 70M records,and get an idea how much time it would take. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design.. Asking again
Man 3 times same thread ! What I could consider to you, I don't really understand what you are getting at, what is wrong with 1000 users firstly ? And in the entry table store their userID which is stored in a session when they login ? So when they enter in data it stores their userID into a column , is that right ? userID = autoinc userID in the user table > -Original Message- > From: Scott Haneda [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 11, 2004 2:50 PM > To: MySql > Subject: Database design.. Asking again > > > Sorry for the post again, I hijacked a thread and wanted to get > this on the > correct track. > > I can not seem to find the section in the manual that talks about the max > number of tables MySql can use, can someone point me please? > > I have been asked to build a database which could have some potentially > interesting storage needs. > > There will be a users table, there can be x users, if all goes > well, x will > be 1000's. > > Each user will be able to upload any number of records, with 100,000 being > the most. Average would be about 10,000 records, but I want to > plan this as > if average was 70,000. > > The 70,000 records will have the following structure: > Id, first name, last name > > So the table will be relatively meager in its storage needs. > Lets call this > table user_contacts. > > If users become day 1000 and each of those users has 70,000 user_contacts, > that would be 70,000 * 1000 total records in one table, as users > grow, this > becomes perhaps too many records in one table. Or at least the potential > for it. > > My next option would be to make a new table, user_contact-userid and make > one for each user, would would then mean, rather than one table with a lot > of records in it, there would be many tables with a max of 100,000 records > in it. > > Can someone share with me their thoughts and suggestions on this? > > > If anyone thinks I should just allow one table to store all this, > with that > table having 70 million records in it, then I can of course go > that road as > well. > -- > - > Scott HanedaTel: 415.898.2602 > http://www.newgeo.com Fax: 313.557.5052 > [EMAIL PROTECTED]Novato, CA U.S.A. > > > -- > 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.. Asking again
Scott Haneda wrote: If users become day 1000 and each of those users has 70,000 user_contacts, that would be 70,000 * 1000 total records in one table, as users grow, this becomes perhaps too many records in one table. Or at least the potential for it. My next option would be to make a new table, user_contact-userid and make one for each user, would would then mean, rather than one table with a lot of records in it, there would be many tables with a max of 100,000 records in it. Can someone share with me their thoughts and suggestions on this? MySQL has a table type called 'merge' tables for this purpose. See http://dev.mysql.com/doc/mysql/en/MERGE.html Basically you create a collection of MyISAM tables, and then define a merge table which you can use to refer to all of them at once. You can then either query the individual tables, or the merge table to get the results you want. As for whether this is necessary, that would depend on the type of data you're storing. How big is each record? If it's just a couple of bits, I think one table for everyone would be OK, even at 70,000,000 records. However if your records are large ( eg contain text column, blob columns, etc ) then merge tables might be the way to go, especially if your data hits the 2GB limit. In your case, with the fields ( ID, FirstName, LastName ), I think you could get away with just one table. I haven't tried anything this big though, so maybe someone who has can enlighten us both. If you don't need any features of InnoDB, I suppose it wouldn't be too much of a hassle setting up merge tables - just in case. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design question
I'm not 100% sure as to what you are trying to do, however, the relationship you describe could, I believe, be modeled as: Candles (candleid, description, price) Waxes (waxid, name/description) Candle_Waxes (candleid, waxid) Thus one table holds the description and price of each candle, another table holds the name of each wax, and a third table connects the two - as a candle can have multiple waxes, the logical way to do this (to me, anyway) is via this third table - glueing the other two together. You'll need to be a bit clever when querying, as simplying joining all three together will bring back multiple rows for candles which contain more than one wax - this could be eliminated by not bringing back the wax details (and using distinct), or in a number of other ways. One other way might be to come up with a way to combine all of the wax names into one field (tricky - can't think how to do this in mysql, off the top of my head). What precisely are you trying to achieve, though - this might be completely wrong for you! Thanks, Matt -Original Message- From: JOHN MEYER [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:39 To: [EMAIL PROTECTED] Subject: Database design question Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Everything I've read about creating online stores is that you are selling inventory items, not the items that makeup the inventory item. So if you sell a red candle made from wax X, candle is the product and red wax X are two attributes of the product. Ideally your structure would work for any product, which means no columns like "waxtype" or "color". Off the top of my head I'm thinking a product table with things like name, description, price, etc. An attribute table. And a productattribute table to link the two. Your attribute table could be a multipurpose table from which you could group your attributes into categories (i.e. wax type, colors, etc.). Something like this: IDPrime IDCategory AttrName AttrDesc The IDCategory field would be a relation to the IDPrime field (a self join). If the IDCategory field is '0', you know it's a "top" level category. This gives you the ability to create a hierarchy with unlimited depth. The data might look like this: 1 0 Candles Burns bright for hours 2 1 Shape Shapes of candles 3 2 Round 4 2 Square 5 2 Pyramid 6 1 Color Colors available 7 6 Red 8 6 Blue 9 6 Gold 10 1 Wax Type 11 10 X 12 10 Y 13 10 Z You can add as many attributes as you like without having to ever change your database structure. So if they are buying a Candle, you search the attributes for Candles and get Shape, Color and Wax type (scent, logo, etc). The user then picks each attribute. If you code it right, you wouldn't even have to change you code when you add attributes and categories. You then store the user selection in the productattribute table. The Product table would be pre-populated with the products you offer, including their links to the attributes. But the design is flexible enough to allow a user to create a custom product or customize and existing one. The shopping cart then only contains the link to the product table. Obviously there is a lot more too creating the whole system, but hopefully this gives you some ideas. On Apr 7, 2004, at 10:39 AM, JOHN MEYER wrote: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
"JOHN MEYER" <[EMAIL PROTECTED]> wrote on 07/04/2004 15:39:10: > Hi, > I'm writing a database for an online candle store. Here's the > situation. This store sells all sorts of items including candles. > Now the gist is that some of the candles can be made in different > types of waxes and some only have one wax. My question is how do I > resolve this when I write up the order and write up the line items. > This almost seems like it is going to be some sort of a three way > join or something. You need to provide more information. For example, do prices differ with different wax types? Are you expecting some sort of validation that candle type A can be manufactured in wax type B? The easiest answer is just to have an extra column "waxtype" and have the ordering application only allow valid settings to be put in it, allowing NULL in the case where there is no choice of waxes. You seem to have discarded this option - but you haven't told us why, so we cannot tell what alternative to suggest. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design, which is better
>I have an question about the design of a database. What is the best way >to design tables. Is it better to create many small tables, or create >fewer big tables. Which of those two options will get the best performance? If you are asking whether to store the data all in one big table or a bunch of smaller identical tables it would be a tradeoff of speed vs. complexity. Normalization can go a long wat to helping. Check out: http://www.vbmysql.com/articles/normalize.html Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design, which is better
Alex croes said: > I have an question about the design of a database. What is the best > way to design tables. Is it better to create many small tables, or > create fewer big tables. Which of those two options will get the > best performance? Don't worry about performance. If performance is really that critical, 4 sentences is not enough to explain the problem. Besides, performance might be different with the next release anyway. Use whichever model represents your data best, because if you normalize your data, you usually don't have that much choice anyway. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design
Duke, Brian wrote: Can someone explain a 1:n non identifying relation? Brian Duke Yes. I'm Dan, and I'm unique; there's only one of me. However if I like a store after an initial purchase from them, I may go back and another purchase. 1:n User:Purchase Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database-design
You may find the SET column type of use here.
http://www.mysql.com/doc/en/SET.html
You could declare column Quarter as
Quarter SET ("one", "two", "three", "four") NOT NULL
or even
Quarter SET ("1", "2", "3", "4") NOT NULL
Then mysql will do some of the work for you.
Michael
Meli Meli wrote:
Thanks to everybody for helping me.
I agree with the suggestion to change the quarter fields in to
one single field. The suggestion from Dan Greene to store the
quarters as binary values sounds good to me. So I will do it this way.
To explain you more about this table. The quarter fields are representing
8 hours of a working day. Each quarter represents 2 hours. First quarter
represents first 2 hours and so on.. I need to register activities made
during the day and if there is an activitie, I need to register in which
quarter of the day. So there made be no activity ore one or to etc.
The year, month, week, and day fields are actually year, cycle, week
and day. This fields are not representing exact dates because the year is
divided in to cycles. So one year has 8 cycles and one cycle has 6 weeks
But also these fields I will combine to one data field and store a binary value.
The table finally looks like:
Id
Quarter ->(binary value)
Eventdate ->(binary value)
Timestamp
Comments
That’s it!
Thanks an regards
Martin
Dan Greene <[EMAIL PROTECTED]> wrote:
I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue...
to store the info more efficiently for what you're saying, you could also use binary as a guide
1 2 3 4
s n r t
t d d h
___
8 4 2 1
- - - -
1000 = 8
0100 = 4
0010 = 2
0001 = 1
1100 = 12
1010 = 10
1001 = 9
0110 = 6
0101 = 5
0011 = 3
1110 = 14
1101 = 13
1011 = 11
0111 = 7
= 15
= 0 (which you don't have below but here for completeness)
and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1
x null null null
null x null null
null null x null
null null null x
x x null null
x null x null
x null null x
null x x null
null x null x
null null x x
x x x null
x x null x
x null x x
null x x x
x x x x
Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 12, 2003 9:01 AM
To: Meli Meli
Cc: [EMAIL PROTECTED]
Subject: Re: Database-design
Why would you created separate fields for each quarter?
Create a field
called quarter and store a number in it. You could also combine year,
month and day into a date field, which would make it easier to search
on ranges.
So, I think your table should look like this:
id
quarter
eventdate
week
On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote:
I have a table with following structure:
Id
first quarter
second quarter
third quarter
last quarter
year
month
week
day
On an entry not all fields of the four quarter fields are
covered with
values.
Following combinations are possible:
first quarter | second quarter | third quarter | last quarter
x null null null
null x null null
null null x null
null null null x
x x null null
x null x null
x null null x
null x x null
null x null x
null null x x
x x x null
x x null x
x null x x
null x x x
x x x x
The table will receive many thousands of entry's.
Would it be better to divide the table in to 15 small
tables in order
to not register fields with null values?
Thanks for helping
Regards Martin
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database-design
Thanks to everybody for helping me. I agree with the suggestion to change the quarter fields in to one single field. The suggestion from Dan Greene to store the quarters as binary values sounds good to me. So I will do it this way. To explain you more about this table. The quarter fields are representing 8 hours of a working day. Each quarter represents 2 hours. First quarter represents first 2 hours and so on.. I need to register activities made during the day and if there is an activitie, I need to register in which quarter of the day. So there made be no activity ore one or to etc. The year, month, week, and day fields are actually year, cycle, week and day. This fields are not representing exact dates because the year is divided in to cycles. So one year has 8 cycles and one cycle has 6 weeks. But also these fields I will combine to one data field and store a binary value. The table finally looks like: Id Quarter ->(binary value) Eventdate ->(binary value) Timestamp Comments Thats it! Thanks an regards Martin Dan Greene <[EMAIL PROTECTED]> wrote: I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue... to store the info more efficiently for what you're saying, you could also use binary as a guide 1 2 3 4 s n r t t d d h ___ 8 4 2 1 - - - - 1000 = 8 0100 = 4 0010 = 2 0001 = 1 1100 = 12 1010 = 10 1001 = 9 0110 = 6 0101 = 5 0011 = 3 1110 = 14 1101 = 13 1011 = 11 0111 = 7 = 15 = 0 (which you don't have below but here for completeness) and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1 > > x null null null > > null x null null > > null null x null > > null null null x > > x x null null > > x null x null > > x null null x > > null x x null > > null x null x > > null null x x > > x x x null > > x x null x > > x null x x > > null x x x > > x x x x Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used > -Original Message- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 9:01 AM > To: Meli Meli > Cc: [EMAIL PROTECTED] > Subject: Re: Database-design > > > Why would you created separate fields for each quarter? > Create a field > called quarter and store a number in it. You could also combine year, > month and day into a date field, which would make it easier to search > on ranges. > So, I think your table should look like this: > id > quarter > eventdate > week > > > On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote: > > > > > I have a table with following structure: > > > > Id > > first quarter > > second quarter > > third quarter > > last quarter > > year > > month > > week > > day > > > > On an entry not all fields of the four quarter fields are > covered with > > values. > > Following combinations are possible: > > > > first quarter | second quarter | third quarter | last quarter > > > > > > > > x null null null > > > > null x null null > > > > null null x null > > > > null null null x > > > > x x null null > > > > x null x null > > > > x null null x > > > > null x x null > > > > null x null x > > > > null null x x > > > > x x x null > > > > x x null x > > > > x null x x > > > > null x x x > > > > x x x x > > > > > > > > The table will receive many thousands of entry's. > > > > Would it be better to divide the table in to 15 small > tables in order > > to not register fields with null values? > > > > > > > > Thanks for helping > > > > Regards Martin > > > > > > > > - > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail AddressGuard > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
RE: Database-design
I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue... to store the info more efficiently for what you're saying, you could also use binary as a guide 1 2 3 4 s n r t t d d h ___ 8 4 2 1 - - - - 1000 = 8 0100 = 4 0010 = 2 0001 = 1 1100 = 12 1010 = 10 1001 = 9 0110 = 6 0101 = 5 0011 = 3 1110 = 14 1101 = 13 1011 = 11 0111 = 7 = 15 = 0 (which you don't have below but here for completeness) and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1 > > xnullnull null > > null x null null > > null null xnull > > null nullnullx > > x x null null > > x null xnull > > x null null x > > null x xnull > > null x nullx > > null null x x > > x x xnull > > x x null x > > x null x x > > null xx x > > x xx x Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used > -Original Message- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 12, 2003 9:01 AM > To: Meli Meli > Cc: [EMAIL PROTECTED] > Subject: Re: Database-design > > > Why would you created separate fields for each quarter? > Create a field > called quarter and store a number in it. You could also combine year, > month and day into a date field, which would make it easier to search > on ranges. > So, I think your table should look like this: > id > quarter > eventdate > week > > > On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote: > > > > > I have a table with following structure: > > > > Id > > first quarter > > second quarter > > third quarter > > last quarter > > year > > month > > week > > day > > > > On an entry not all fields of the four quarter fields are > covered with > > values. > > Following combinations are possible: > > > > first quarter | second quarter | third quarter | last quarter > > > > > > > > xnullnull null > > > > null x null null > > > > null null xnull > > > > null nullnullx > > > > x x null null > > > > x null xnull > > > > x null null x > > > > null x xnull > > > > null x nullx > > > > null null x x > > > > x x xnull > > > > x x null x > > > > x null x x > > > > null xx x > > > > x xx x > > > > > > > > The table will receive many thousands of entry's. > > > > Would it be better to divide the table in to 15 small > tables in order > > to not register fields with null values? > > > > > > > > Thanks for helping > > > > Regards Martin > > > > > > > > - > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail AddressGuard > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database-design
Why would you created separate fields for each quarter? Create a field called quarter and store a number in it. You could also combine year, month and day into a date field, which would make it easier to search on ranges. So, I think your table should look like this: id quarter eventdate week On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote: I have a table with following structure: Id first quarter second quarter third quarter last quarter year month week day On an entry not all fields of the four quarter fields are covered with values. Following combinations are possible: first quarter | second quarter | third quarter | last quarter xnullnull null null x null null null null xnull null nullnullx x x null null x null xnull x null null x null x xnull null x nullx null null x x x x xnull x x null x x null x x null xx x x xx x The table will receive many thousands of entrys. Would it be better to divide the table in to 15 small tables in order to not register fields with null values? Thanks for helping Regards Martin - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database-design
you need to normalize your database structure, and create an ER diagram based on that. On Tue, 11 Nov 2003, Meli Meli wrote: > > I have a table with following structure: > > Id > first quarter > second quarter > third quarter > last quarter > year > month > week > day > > On an entry not all fields of the four quarter fields are covered with values. > Following combinations are possible: > > first quarter | second quarter | third quarter | last quarter > > > > xnullnull null > > null x null null > > null null xnull > > null nullnullx > > x x null null > > x null xnull > > x null null x > > null x xnull > > null x nullx > > null null x x > > x x xnull > > x x null x > > x null x x > > null xx x > > x xx x > > > > The table will receive many thousands of entrys. > > Would it be better to divide the table in to 15 small tables in order to not > register fields with null values? > > > > Thanks for helping > > Regards Martin > > > > - > Do you Yahoo!? > Protect your identity with Yahoo! Mail AddressGuard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database-design
> On an entry not all fields of the four quarter fields are covered with > values. Following combinations are possible: Why don't you just use one extra column (apart from whatever other data you are storing) with that column being a date field. Then, you can determine what data is part of what time period as part of the query. Perhaps I'm not understanding what your ultimate need is, but I don't see any reason why you would need a schema such as the example you provided or 15 extra tables. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design and Bianry Operations
by 4 bytes number (as 1 field) , you can keep 32 bits (criteria). this shows how much you can save the space. but if the database is not huge, it's better to select simpler solution (one field for one criteria). this is an example in MySQL manual to show how to work with bits: The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page. CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23); SELECT year,month,BIT_COUNT(BIT_OR(1< To: <[EMAIL PROTECTED]> Sent: Tuesday, October 14, 2003 9:17 AM Subject: Database Design and Bianry Operations > Hello, > > I'm after some advice on database design: > > I've got an object - for an example a hotel - and I want to keep information > about this hotel, criteria that it either has or hasn't (TV, swimming pool > etc). > > I want to search on criteria and return the most appropriate match. > > Bearing in mind I've currently got over 200 criteria and want to expand > this, how should approach the design of my criteria table. > > Should I have a table with Hotel Id and then a char(1) (Y/N) field for each > criteria and then a seperate look-up table for criteria name. > > I get the feeling there is a more efficient method using binary operations > (only one field populated with zeros and ones) but I can't find anything to > help in the manuals. > > Any thoughts? > > Lee Denny > > > -- > 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 and Bianry Operations
Lee, > I get the feeling there is a more efficient method using > binary operations > (only one field populated with zeros and ones) but I can't > find anything to > help in the manuals. i think you should try to avoid such thing as much as you can. Storing more than one piece of data in a column may bring up a lot of problems if there is a need for changing structures one day. It won't save you from having a criteria-explaining table anyway. As long as you don't want to store data for every hotel of the world, a solution might be to store one record for each criteria for each hotel: hotel-id | criteria-id | value --- rec1: | hotel1 | criteria1 | true rec2: | hotel1 | criteria5 | false Maybe there is no need for records having a false. Regards, TomH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Design and Bianry Operations
Lee. Maybe others would disagree but they way I would handle this would be to forget about storing the data in separate fields (or binary fields) and use three tables. Table1 - Facilities A complete list of all criteria you want to record about the hotels Table2 - Hotels Table containing the hotel's basic details Table3 - Hotel Facilities Table allowing links to be made between hotels and facilities. There will be one record for each facility for each hotel. The advantage of the above is that you don't have to worry when it is time to record new facilities or remove references to facilities as all you need to do is insert/delete records from the relevant tables. I think storing the facilities in separate fields or one field will make life difficult when changing the data you wish to record. Searching is also easy as all you have to do is perform a join across the relevant tables. HTH Kev. > -- > From: Lee Denny > Sent: Tuesday, October 14, 2003 14:17 PM > To: [EMAIL PROTECTED] > Subject: Database Design and Bianry Operations > > Hello, > > I'm after some advice on database design: > > I've got an object - for an example a hotel - and I want to keep > information > about this hotel, criteria that it either has or hasn't (TV, swimming pool > etc). > > I want to search on criteria and return the most appropriate match. > > Bearing in mind I've currently got over 200 criteria and want to expand > this, how should approach the design of my criteria table. > > Should I have a table with Hotel Id and then a char(1) (Y/N) field for > each > criteria and then a seperate look-up table for criteria name. > > I get the feeling there is a more efficient method using binary operations > (only one field populated with zeros and ones) but I can't find anything > to > help in the manuals. > > Any thoughts? > > Lee Denny > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > > > E-MAIL DISCLAIMER: The information in this email is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. All information provided, including but not limited to, quotations, system specifications and suggestions concerning hardware/software (and services) configurations are strictly subject to our standard terms and conditions of business, copies of which are available on request. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design and Bianry Operations
Hi, I would create one field (char(200) or char(255)) for all criteria. After that I will store: in the first byte of that string Y if hotel has swimming pool, N - doesn't have, Q - no idea in the second byte of that string Y if hotel has TV, N - doesn't have, Q - no idea in the third byte of that string Y if hotel has fitness club, N - doesn't have, Q - no idea ... After that I will be able to select from that table data according to my criteria: select * from Hotel where Criteria like 'NY_' It means select all hotels that: swimming pool - don't have, TV - have fitness club - doesn't matter Best regards, Mikhail. - Original Message - From: "Lee Denny" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 14, 2003 3:17 PM Subject: Database Design and Bianry Operations > Hello, > > I'm after some advice on database design: > > I've got an object - for an example a hotel - and I want to keep information > about this hotel, criteria that it either has or hasn't (TV, swimming pool > etc). > > I want to search on criteria and return the most appropriate match. > > Bearing in mind I've currently got over 200 criteria and want to expand > this, how should approach the design of my criteria table. > > Should I have a table with Hotel Id and then a char(1) (Y/N) field for each > criteria and then a seperate look-up table for criteria name. > > I get the feeling there is a more efficient method using binary operations > (only one field populated with zeros and ones) but I can't find anything to > help in the manuals. > > Any thoughts? > > Lee Denny > > > -- > 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 Question...
On 18-Jun-2003 NIPP, SCOTT V (SBCSI) wrote: > Hello... I am currently working on a User Account Management > system. I am actually a Unix SA who is "moonlighting" at work as a MySQL > DBA and web developer. I am learning a lot and enjoying the work, but I > am > severely lacking in database design fundamentals. I have created a > couple > very simple databases, but my two newest projects are getting more > sophisticated. I was hoping for some DB design help with the following > example... > > Let's say that I have users Mary, Joe, Frank, and Dan. I also have > servers panther, cheetah, jaguar and lion. The data for each account > that I > want to maintain is UID, GID, home directory, and default shell. > In designing a table or tables to handle this example what can I > make as a primary key? My idea was to have a table named mary, with a > row > for each server, and each column would hold the data such as UID, GID, > etc. > This would mean that the primary key for each row would simply be the > server > name. > By holding all of the data, including server name, in a single > table, I am not sure how I would define a primary key. I couldn't use > the > user name or server name as there would be duplication. I suppose I > could > use a dummy numeric field that is auto-incrementing, but I am not sure > how > good an idea this is. I think I have read somewhere that you can > actually > use a combination of multiple columns as a primary key or index, but this > is > something I am obviously not familiar with. > One other concern I have is regarding performance. The database > work I have done so far has been dealing with relatively miniscule > amounts > of data. This database table however is going to contain information for > about 80 servers with somewhere around 300 users per server on average. > This is quite a large number of rows from my very limited experience. I > don't want to come up with a poor table design that ends up causing > problems > down the line. > > Well, that's about all I can think of at the moment. I am sure that > I will have plenty more questions as this progresses. Thanks again for > the > feedback. > 300 * 80 =24,000 rows --this is _NOT_ a lot. At first, I'd use just one table: srvr varchar(32) NOT NULL, login varchar(32) NOT NULL, uid smallint unsigned NOT NULL DEFAULT 1000, gid smallint unsigned NOT NULL DEFAULT 1000, gecos varchar(128), sh varchar(32) NOT NULL DEFAULT '/bin/sh', home varchar(64), ... more fields ... primary key (login,srvr)) A next refinement would change the 'srvr' field to: srvr tinyint unsigned and create a 'server' table: id tinyint unsigned AUTO_INCREMENT, // last octet of ip ? name varchar(64) NOT NULL, primary key (id)) That'll make it easy to rename servers. The table(s) could be populated very easily with a couple of shell, Perl, and/or awk scripts. YP/NIS would come in handy as well. You'd loop on each distinct server name --request a ypxfer of the passwd map, then suck it into your table. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Design Question...
[snip] > Let's say that I have users Mary, Joe, Frank, and Dan. I also have > servers panther, cheetah, jaguar and lion. The data for each account that I > want to maintain is UID, GID, home directory, and default shell. > In designing a table or tables to handle this example what can I > make as a primary key? My idea was to have a table named mary, with a row With the name "mary", are you implying a separate table for each user? If so, that doesn't sound right. I think you should have three tables: (1) A table of users, with properties that depend on the user but not those that might vary with server; (2) A table of servers, with properties that are indepedent of users; (3) A table with primary key formed by a pair of foreign keys, one pointing at the user table, one at the server table. This table would have properties that depend on the user/server combination. (An example would be the shell, since presumably a user could have different shells on different servers.) > for each server, and each column would hold the data such as UID, GID, etc. > This would mean that the primary key for each row would simply be the server > name. > By holding all of the data, including server name, in a single > table, I am not sure how I would define a primary key. I couldn't use the > user name or server name as there would be duplication. I suppose I could > use a dummy numeric field that is auto-incrementing, but I am not sure how > good an idea this is. I think I have read somewhere that you can actually > use a combination of multiple columns as a primary key or index, but this is > something I am obviously not familiar with. > One other concern I have is regarding performance. The database > work I have done so far has been dealing with relatively miniscule amounts > of data. This database table however is going to contain information for > about 80 servers with somewhere around 300 users per server on average. > This is quite a large number of rows from my very limited experience. I Nah, doesn't sound like much data to me. > don't want to come up with a poor table design that ends up causing problems > down the line. [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design : 3 month later
I don't enjoy being a PITA but could you teach your MUA to create proper attributions? This makes it really hard to tell who said what, leads to confusion, and may piss of quite a few people if a debate gets somewhat heated... # [EMAIL PROTECTED] / 2003-04-02 18:18:17 +0200: > > # [EMAIL PROTECTED] / 2003-04-02 17:37:26 +0200: > > > And from this page: > > > http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html > > > I understand it is rather recommended NOT to use foreign keys, at > > > least until mysql integrates it more fully. > > > > which part of that page makes you say that? > > I mean, at least that's how I reacted upon reading.. > " > For MyISAM tables, you can work around the lack of ON DELETE by adding > the appropriate DELETE statement to an application when you delete > records from a table that has a foreign key. In practice this is as > quick (in some cases quicker) and much more portable than using foreign > keys. > " That doesn't say "we do not recommend using FKs with MySQL". It says "FKs don't work with MyISAM tables". > and > " > A properly written application will make sure internally that it is not > violating referential integrity constraints before proceding with a > query. Thus, additional checks on the database level will only slow down > performance for such an application. > It is not uncommon for a DBA to make such a complex topology of > relations that it becomes very difficult, and in some cases impossible, > to back up or restore individual tables. > " I read this: "an excuse for not having implemented FKs". Yes, FK checks will slower the queries in the server, but you have to do it in the client, so what have you gained? Plus, if you have more than one interface to the database, you have to reimplement the integrity checks over and over, which is error-prone. And yes, I've seen a large database application (on Sybase; there was only one client written) in which the programmers decided to *not* use FKs because the checks brought the ASE to its knees. > and I've got the feeling that the only advantage is that I could delete > all related rows in tables that have the foreign key in one query > instead of three or more(take the foreignkey values, delete the row, > delete the rows in the related tables one by one..) that's not the only advantage, although the MySQL authors probably wouldn't mind if you didn't expect more from your life. :) > That is an interestesting point because each time I have to code that > in an application, it's error prone and it's not exciting. Exactly. -- FreeBSD 4.8-RC 6:55PM up 9 days, 9:15, 20 users, load averages: 0.04, 0.03, 0.04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database design : 3 month later
I mean, at least that's how I reacted upon reading.. " For MyISAM tables, you can work around the lack of ON DELETE by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys. " and " A properly written application will make sure internally that it is not violating referential integrity constraints before proceding with a query. Thus, additional checks on the database level will only slow down performance for such an application. It is not uncommon for a DBA to make such a complex topology of relations that it becomes very difficult, and in some cases impossible, to back up or restore individual tables. " and I've got the feeling that the only advantage is that I could delete all related rows in tables that have the foreign key in one query instead of three or more(take the foreignkey values, delete the row, delete the rows in the related tables one by one..) That is an interestesting point because each time I have to code that in an application, it's error prone and it's not exciting. Cheers, Cordialement, Damien COLA http://www.VarMalin.com -Original Message- # [EMAIL PROTECTED] / 2003-04-02 17:37:26 +0200: > And from this page: > http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html > I understand it is rather recommended NOT to use foreign keys, at > least until mysql integrates it more fully. which part of that page makes you say that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design : 3 month later
# [EMAIL PROTECTED] / 2003-04-02 17:37:26 +0200: > And from this page: > http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html > I understand it is rather recommended NOT to use foreign keys, at least > until mysql integrates it more fully. which part of that page makes you say that? -- FreeBSD 4.8-RC 6:05PM up 9 days, 8:25, 20 users, load averages: 0.06, 0.08, 0.03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database design : 3 month later
Thank you for your explanations, after 4 interventions I now know much more about the difference between primary, unique, index and I've learnt that foreign keys exits in my sql ;-) And from this page: http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html I understand it is rather recommended NOT to use foreign keys, at least until mysql integrates it more fully. Cheers to people who replied, I am now a little bit more knowledgable about this topic and will be able ot helps others when the opportunity will arise. Have a nice day, Cordialement, Damien COLA http://www.VarMalin.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design : 3 month later
X-Mailer: Microsoft Outlook, Build 10.0.2616 your MUA wrap-mutilates quoted text. # [EMAIL PROTECTED] / 2003-04-02 02:34:01 +0200: > > 1. If a column is a primary key, there is no need to declare it a > > unique. > > Thank you, I was doing that for every table since I started. http://www.mysql.com/doc/en/CREATE_TABLE.html > > 2. Without foreign keys, your CREATE statements don't reveal table > > relationships. > > I am surprised, I thought my SQL is not a proper relationnal database > and has no embedded way of specifying the foreign key. http://www.mysql.com/doc/en/CREATE_TABLE.html http://www.mysql.com/doc/en/example-Foreign_keys.html > For my part I am doing : > Have a field in attached table for 1-n relationships > Have another table (3) with table1.id and table2.id for n-n > relationships > What is the proper way then ? you are doing it right, but it's not obvious from the DDL. > ps: I still have my SQL dump if you want to have another look.. it's in the ml archives, no need to resend it. -- FreeBSD 4.8-RC 4:31PM up 9 days, 6:50, 20 users, load averages: 0.20, 0.08, 0.02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database design : 3 month later
Peter, >1. If a column is a primary key, there is no need to declare it a unique. Thank you, I was doing that for every table since I started. >2. Without foreign keys, your CREATE statements don't reveal table relationships. I am surprised, I thought my SQL is not a proper relationnal database and has no embedded way of specifying the foreign key. For my part I am doing : Have a field in attached table for 1-n relationships Have another table (3) with table1.id and table2.id for n-n relationships What is the proper way then ? >3. The best way I know to validate a schema is to (i) write or draw all the application's use cases, (ii) from the use >cases derive all the required data items, (iii) organise these items into a structure diagram with a modelling tool like >Microsoft Visio or Dezign, (iv) have the modelling tool generate the database, (v) populate the database with a bit of >test data and (vi) see if you can derive (with or without pseudocode) all the system's required outputs. The crucial tests >are step (iv) anf (vi). I have no such program to help design a database, but I have to agree this STEP seems like an optmized way of doing things. I'll try get one such program in the public domain perhaps. Thank you. ps: I still have my SQL dump if you want to have another look.. each time there's a 'simulated' foreign key, it's at the end of the fields and has the nameOfTheTableID naming scheme. For example for displaying a menu, I need to query the tables tgl_item, tgl_course. Since items are classified by course, I've put the simulated foreign key of tgl_course in tgl_item For example when I want to know if a restaurant is open at this time or not, I have all the possible times in tgl_time and the times registered for that restaurant in tgl_rel_rest_time (restID, timeID, weekday) so the three fields of tgl_rel_rest_time form a primary key, but there will be about 300-400 rows for one restaurant.. I don't know if putting a primary key on 3 fields is a good thing to do for speed and storage.. I have no experience in fact. Cheers, Damien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design : 3 month later
Damien, 1. If a column is a primary key, there is no need to declare it a unique. 2. Without foreign keys, your CREATE statements don't reveal table relationships. 3. The best way I know to validate a schema is to (i) write or draw all the application's use cases, (ii) from the use cases derive all the required data items, (iii) organise these items into a structure diagram with a modelling tool like Microsoft Visio or Dezign, (iv) have the modelling tool generate the database, (v) populate the database with a bit of test data and (vi) see if you can derive (with or without pseudocode) all the system's required outputs. The crucial tests are step (iv) anf (vi). PB [filter fodder: mySQL] - - Original Message - From: Damien COLA To: [EMAIL PROTECTED] Sent: Sunday, March 30, 2003 9:49 AM Subject: database design : 3 month later Hello, I was starting a php/mysql project in december and seeked for help on this mailing list, it has been a great help for solving problems. My application is a website where you can order meals from restaurants that are within your postcode area (UK only) The database structure has grown up to 30 tables. My biggest project yet. I would like to validate the database structure to SQL experts, in particular regarding mySQL indexes that I am not sure if it'll help or not, as there should be lots of select query while browsing the restaurants/menus. And I wonder how it will manage when there'll be 1000 restaurants or more. Here the sql dump of the main tables, please don't get scared ;-) CREATE TABLE `tgl_address` ( `addressID` int(11) NOT NULL auto_increment, `address` varchar(255) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `city` varchar(30) NOT NULL default 'London', `website` varchar(255) NOT NULL default '', `email` varchar(100) NOT NULL default '', `phone` varchar(20) NOT NULL default '', `fax` varchar(20) NOT NULL default '', `sms` varchar(20) NOT NULL default '', `contact` varchar(20) NOT NULL default '', PRIMARY KEY (`addressID`), UNIQUE KEY `addressID` (`addressID`) ) TYPE=MyISAM;
Re: database design : 3 month later
# [EMAIL PROTECTED] / 2003-03-30 17:49:27 +0200: > Hello, I was starting a php/mysql project in december and seeked for help on > this mailing list, it has been a great help for solving problems. > My application is a website where you can order meals from restaurants that > are within your postcode area (UK only) > > The database structure has grown up to 30 tables. My biggest project yet. > I would like to validate the database structure to SQL experts, in > particular regarding mySQL indexes that I am not sure if it'll help or not, > as there should be lots of select query while browsing the > restaurants/menus. > And I wonder how it will manage when there'll be 1000 restaurants or more. > > Here the sql dump of the main tables, please don't get scared ;-) > > CREATE TABLE `tgl_address` ( > `addressID` int(11) NOT NULL auto_increment, > `address` varchar(255) NOT NULL default '', > `postcode` varchar(10) NOT NULL default '', > `city` varchar(30) NOT NULL default 'London', > `website` varchar(255) NOT NULL default '', > `email` varchar(100) NOT NULL default '', > `phone` varchar(20) NOT NULL default '', > `fax` varchar(20) NOT NULL default '', > `sms` varchar(20) NOT NULL default '', > `contact` varchar(20) NOT NULL default '', > PRIMARY KEY (`addressID`), > UNIQUE KEY `addressID` (`addressID`) this is unnecessary duplication (...) the table structure is not very interesting without seeing how the tables are related. could you post a schema somewhere on the web, or otherwise describe the relations? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Design Question
Thanks very much to every who helped me with my MySQL problem! I will probably go with the three table solution as it also eliminates the need for yet another table! Cheers, Ben - 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: Database Design Question
> -Original Message- > From: DL Neil [mailto:[EMAIL PROTECTED]] ... > Which begs the questions: > - in what way are you analyzing "behavior"? and > - in what way are you analyzing this list-community? "There's too much to read," is the simple answer to the first question. Over the last few years, I've developed some methods of identifying the most influential people in on-line discussions by discovering social networks. Among them, I'm quite sure, are people who are opinion leaders. By identifying them, it's possible to track ideas and trends. Not long after I first started doing this, I did some brainstorming about it with O'Reilly & Associates, which uses such techniques to help decide which open source projects have momentum, so that they can make better decisions about what books to publish. In fact, MySQL v. mSQL was one of the early areas they studied. I also started a company, Opion Inc., to do this kind of analysis for the stock market, movies and other areas. Opion recently sold its technology to Intelliseek (and I have no idea what they're doing with it, but it's not hard to imagine). They seem to have a server on-line at www.opion.com, but it's secured. As for this community, my "analysis" is limited to reading the messages that either have interesting subjects or senders. By eyeballing the folder that contains them, that is, since my software tries to figure out what's interesting as well. Just to put some fears to rest, which usually come up quickly, I long ago recognized that the list of influential people is something of a spammer's dream. Equally long ago, it was obvious that such lists have to be unavailable to anyone else. It's an interesting, familiar paradox -- how to use the data without revealing enough that it could be abused. I don't want to get any further into the various issues around this research here unless it has something to do directly with MySQL. Nick - 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: Database Design Question
Nick, > This is pretty familiar to me because I'm analyzing the behavior of people > in on-line discussions, so I'm gathering such data. Which begs the questions: - in what way are you analyzing "behavior"? and - in what way are you analyzing this list-community? =dn MySQL list busting - 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: Database Design Question
In your situation (or any situation, IMHO), multi-value fields defeat the purpose of good database design. I think you are on the right track if you are willing to tolerate a little redundancy for the sake of simplicity (as a one-to-many relationship). If you really want to structure it as a many-to-many relationship, then try the following (I throw my 2 cents without knowing exactly what you are dealing with in terms of data structures): Table 1: list (entity table) listId, owner, date, ... Table 2: entry (entity table) entryId, entryDetails (attribute 1, 2, 3, ...)... Note: Lump all entries from different lists together and use an auto_increment primary key and a unique key constraint for every entry. Table 3: listEntry (relation table) listId, entryId Such as: A, entry10 B, entry20 B, entry65 C, entry65 D, entry50 Note: Make (listId, entryId) a primary key or at least a unique key. Hope this helps. Jack --- Ben Holness <[EMAIL PROTECTED]> wrote: > Hi Chris/Nick/Scalper, > > Thanks for the replies. I am not too sure how to > implement this in tables, > so I will give an example: > > Let's say I have three lists - a,b and c. List a > contains 10,000 entries, > list b contains 2,500 entries and list c contains > 75,000 entries. > > I have a table of lists, with list ID, owner and > date fields: > a,Ben,28/03/2002 > b,Ben,28/03/2002 > c,Tom,28/03/2002 > > Then I have another table for each list, with all of > the entries. The fields > would be list ID and entry: > > a,entry1 > a,entry2 > a,entry3 > ... > b,entry1 > b,entry2 > ... > c,entry1 > c,entry2 > c,entry3 > ... > > Have I understood the two table concept correctly? > How does the third table fit in? > > I guess that if two (or more) of the entries > overlap, I could make things > even better by having a multi-value field for the > list ID in the second > table, allowing: > > b&c,entry65 > > if entry65 was common to lists b and c ... Is there > a limit to the number of > values in a multi-value field? (Does MySQL offer > multivalue fields?) and > would this be appropriate? > > Cheers, > > Ben > > > > I would recommend: > > > > A table of lists (one row for each list with > owner, ID and > > creation Date > > (add modification date?)) > > > > A table of list entries (one row for each entry) > > A third table linking the list to list entry. > > > - > 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!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.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
RE: Database Design Question
> -Original Message- > From: Ben Holness [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 28, 2002 7:43 AM > Have I understood the two table concept correctly? > How does the third table fit in? > > I guess that if two (or more) of the entries overlap, I could make things > even better by having a multi-value field for the list ID in the second > table, allowing: > > b&c,entry65 > > if entry65 was common to lists b and c ... Is there a limit to > the number of > values in a multi-value field? (Does MySQL offer multivalue fields?) and > would this be appropriate? I'm assuming that by distribution list, you mean you're storing something like e-mail addresses? And now I have to take back the notion that storing the lists in a file would be efficient at all. If you're going to manipulate individual items (name, address, whatever) in the list, by all means put them in the database as rows. I think that only two tables would be needed -- one that I'd call "owner," with the data that relates to the owners of lists, with the list ID as the primary key. The second would be for entries (subscriber?), with the list ID as a foreign key (not that MySQL knows about foreign keys). Your overlap is handled with additional rows. For example, if [EMAIL PROTECTED] is in lists a and b, then there are two records for joe -- one that contains "a,[EMAIL PROTECTED]" and anther with "b,[EMAIL PROTECTED]". A third table would just complicate things. The primary key would be the list ID and the address, in this example, which would ensure that you wouldn't have duplicate entries. Then a query such as "select * from subscriber where list_id='a'" would find everybody subscribed to list a. "select * from subscriber where subscriber='[EMAIL PROTECTED]'" would find all the lists to which joe is subscribed. This is pretty familiar to me because I'm analyzing the behavior of people in on-line discussions, so I'm gathering such data. Nick - 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: Database Design Question
Hi Chris/Nick/Scalper, Thanks for the replies. I am not too sure how to implement this in tables, so I will give an example: Let's say I have three lists - a,b and c. List a contains 10,000 entries, list b contains 2,500 entries and list c contains 75,000 entries. I have a table of lists, with list ID, owner and date fields: a,Ben,28/03/2002 b,Ben,28/03/2002 c,Tom,28/03/2002 Then I have another table for each list, with all of the entries. The fields would be list ID and entry: a,entry1 a,entry2 a,entry3 ... b,entry1 b,entry2 ... c,entry1 c,entry2 c,entry3 ... Have I understood the two table concept correctly? How does the third table fit in? I guess that if two (or more) of the entries overlap, I could make things even better by having a multi-value field for the list ID in the second table, allowing: b&c,entry65 if entry65 was common to lists b and c ... Is there a limit to the number of values in a multi-value field? (Does MySQL offer multivalue fields?) and would this be appropriate? Cheers, Ben > I would recommend: > > A table of lists (one row for each list with owner, ID and > creation Date > (add modification date?)) > > A table of list entries (one row for each entry) > A third table linking the list to list entry. - 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: Database Design Question
Hi Ben: Performance would definitely be better if you store the data in the database as opposed to simply storing references to files. Sounds like you could solve this with 2 tables with one for the actual lists (assuming the structure of all your list is the same) and the other for the list header info (like owner and creation date). Craig At 07:39 AM 3/28/2002, you wrote: >Hi all, > >I am in the process of designing a MySQL database that will run on Redhat >7.1, but I am fairly sketchy on MySQL and performance. > >What I want to do is have a database that keeps track of large distribution >lists. Each list has a unique ID, an owner (which is a reference to an ID in >another table) and a creation date. > >My question is this: Would it be more efficient to have each entry in the >list stored in this table in the database, or would I be better off having a >reference to a file that is stored on the hard disk? > >Users must be able to view, add, modify and delete entire lists and single >entries in the lists. > >I am using MySQL, PHP and Perl with an Apache Webserver. > >Cheers, > >Ben > > >- >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: Database Design Question
> -Original Message- > From: Ben Holness [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] ... > My question is this: Would it be more efficient to have each entry in the > list stored in this table in the database, or would I be better > off having a > reference to a file that is stored on the hard disk? If you mean efficient in terms of performance, the answer depends on how big the data is that would go in the files v. a database record, and how often they'll be accessed. If you mean more efficient in terms of disk space, there's probably little difference if the files are large, but a big difference if there are lots of little ones. How much difference for the latter depends on what file system you're using. In any event, you'll probably have to experiment. If you're talking about a lot of relatively small files, putting the data into database fields will probably win. Nick - 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: Database Design Question
On 3/28/02 1:39 PM [EMAIL PROTECTED] wrote: > > What I want to do is have a database that keeps track of large distribution > lists. Each list has a unique ID, an owner (which is a reference to an ID in > another table) and a creation date. > > My question is this: Would it be more efficient to have each entry in the > list stored in this table in the database, or would I be better off having a > reference to a file that is stored on the hard disk? > > Users must be able to view, add, modify and delete entire lists and single > entries in the lists. > I would recommend: A table of lists (one row for each list with owner, ID and creation Date (add modification date?)) A table of list entries (one row for each entry) A third table linking the list to list entry. You may have to parse the lists at first to separate the list entries but this method maintains a lot of flexibility: To delete an entire list remove the row from the lists table. This would allow you to maintain the list entries separately or you could also use a "DELETE FROM linkingTable WHERE listID=$whatever". Deleting individual records would involve a single row in the list entry table and a "DELETE FROM linkingTable WHERE listEntryID=$whatever". You probably would not have to sacrifice speed for flexibility: in small tables (<200 records with an index) this type of operation takes less than .001 of a second on my (shared) server. Hope this helps! Chris -- Chris Adams Cypress Lake Studios Hypermedia, Quicktime, and Internet Design http://www.cypresslakestudios.com [EMAIL PROTECTED] - 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: Database Design
i got a really good book on MySQL, it does not have much in the way of troubleshooting, but a lot on use. Title: MySQL Author: Paul DuBois ISBN: 0-7357-0921-1 Publisher: New Riders - 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: Database Design
==>Would someone suggest a book that's more related to database design and preferably have some real-world scenarios as examples. "Database Design for Mere Mortals A Hands on Guide to Relational Database Design" Michael J. Hernandez Colm Colm Brazel (MA) CB Publications www.cbweb.net [EMAIL PROTECTED] - Original Message - From: "Jindo Soul" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 11, 2001 2:43 PM Subject: Re: Database Design > Hi All, > > Not really a MySQL-oriented question. But this board's so active I decided to post my question here in hope I'll get some lights on my database design. > > Using Amazon as an example, say if I have a table that stores user information and each user is assigned an unique id (field might look like "id int unsigned primary key "). What design should I go after? Should I go for one big table with a definition that might look like > > CREATE TABLE TransactionRecord ( > user_id int unsigned, > book_id int unsigned > index(user_id), > index(book_id) > ); > > then just insert one row for each book bought by a particular user? I was worrying that the user_id field might have a lot of repeated, identical value (one user has purchased many books). The book_id will just be worse if a popular book is sold like 500K copies. I was thinking lots of identical value in one field will simply cancel out the use of index. > > Do I have other design alternatives? Shed me some lights if you can :) > > I've got quite a few MySQL books on hand, but they focus on teaching readers what MySQL can do and how to get the most out of MySQL. Would someone suggest a book that's more related to database design and preferably have some real-world scenarios as examples. > > Regards, > > Jindo > > - > 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: Database Design
Hi All, Not really a MySQL-oriented question. But this board's so active I decided to post my question here in hope I'll get some lights on my database design. Using Amazon as an example, say if I have a table that stores user information and each user is assigned an unique id (field might look like "id int unsigned primary key "). What design should I go after? Should I go for one big table with a definition that might look like CREATE TABLE TransactionRecord ( user_id int unsigned, book_id int unsigned index(user_id), index(book_id) ); then just insert one row for each book bought by a particular user? I was worrying that the user_id field might have a lot of repeated, identical value (one user has purchased many books). The book_id will just be worse if a popular book is sold like 500K copies. I was thinking lots of identical value in one field will simply cancel out the use of index. Do I have other design alternatives? Shed me some lights if you can :) I've got quite a few MySQL books on hand, but they focus on teaching readers what MySQL can do and how to get the most out of MySQL. Would someone suggest a book that's more related to database design and preferably have some real-world scenarios as examples. Regards, Jindo - 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: Database design question
On Saturday 28 July 2001 15:09, Scott Goldstein wrote: > I'm new to MySQL and database design and I have a questions concerning > entities with common attributes. > > Suppose I have two entities, foo and bar with the following attributes: > > foo: (id, A, B, C, D, F) > bar: (id, A, B, C, X, Y) Well, I just played with it here, using an addressbook and Zipcode tables and it seemed to work, should work for you too:-) select * from foo,bar where foo.id=bar.id; You would put 'A,B,C, in one of the tables then based on the id's of the two tables, all the fields would then be displayed:-) > > Both entities have several attributes in common. Is there any way to > take advantage of this? I recall reading the MySQL does not support > table inheritance. Is there any other mechanism that I can use? > > Thanks for the help. > > Scott > > > - > 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: database design
I am sure this has been recommended on this list before, but here it goes: One very good book to read regarding theory is: Database Design for Mere Mortals by Michael Hernandez, it sits along with Paul DuBois' book on my night table. I don't think you can pick a better combination to teach yourself RDBMS and MySQL, than those two. Philippe - Original Message - From: AAAunderground <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 18, 2001 10:53 PM Subject: database design Is it better to have very small tables with a max of 3 rows, slightly larger tables with several indexed columns. I am designing a database for a medium size company. I certainly plan to expand the business and the database. I want to make sure that I plan the DB correctly from the beginning. I am thinking that most tables will use an autogen primary key. Does anyone have a link to a good source of reading for database design? Maybe recommended dead tree reading? Thanks -Spencer Underground deltree c:\windows /y - 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: database design
I counldn't agree more with this email that was sent by Jay. I really don't see how tables with only three columns would be an advantage to any systems. On the contrary, I think it would be complicate to select, insert, update and delete information. The ecommerce example that I posted in my page is about a company that sells "coins" via web. Let's think of product table with 3 fields only: ProductID, Name, and Price. However coins have other informations that would define them, and that are simple attributes. So no reason to separate them in another table or tables I also agree that most books do not cover 'Relational Theory vs Relational Reality' very well. It requires time to learn how to model systems, and I do encourage people to read ,and also identify who does it well and stick with them to understand how they think. Sometimes you come up with an ERD diagram for a system that can have some denormalization. Just keep in mind that some denormalization is necessary depending on the situation. In my ecommerce example, for example, when an order is shipped, I put inside of order information about the chosen shipping address, and as a business rule I do not allow shipped orders to be updated. This way I garantee that all orders will contain the shipping address chosen by the customer in a specific time, I will not have to keep history of addresses in case they are update, and I can allow the customer even delete that specific address from his address book. The same I do for the price of item. Once the order is shipped, the price is placed inside of Orderitems table and no updates are allowed in any line of taht order. The company can change the price of the product in table product and this will not affect old orders. I do agree that this is a simple way of dealing with it. Maybe with product price I should keep a history of changes. However I tried to make it simple for this example. Siomara >From: jay downs <[EMAIL PROTECTED]> >To: AAAunderground <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]" ><[EMAIL PROTECTED]> >Subject: Re: database design >Date: Tue, 19 Jun 2001 13:54:58 -0400 > >Alot of it would depend on what exactly you are doing. If you have alot of >small >tables with only 3 columns(I'm assuming thats what you meant) that can make >for a >messy server when it starts getting hit by a demand. Also, there are lots >of good >books on database design. Que generally has made some pretty good books >and are >available at most borders or barnes& noble. In most cases the theory is >still the >same when it comes to relational databases. One thing that I dont think >that most >books go over is what I like to call 'Relational Theory vs Relational >Reality'. >Sometimes using a relational design, causes a significant overhead on the >machine >and also there are situations when you are obtaining data for the data >collecting >tables, you might want the opportunity to copy the data over rather than >refer to >the data table being looked to for this data. This will increase the size >of the >database, but overcomes situations such as 'What if the part# changes >descriptions >slightly?' If you're still using the same part#, but different description >and you >have to refer back to line item (I'm using a basic inventory system layout >here), >your customer could be looking at one thing and you another and a breakdown >in the >communication occurs because now the info isnt the same. Alot of times, it >is via >experimentation that you learn this sort of thing. Other times, its >because you've >painted yourself into a corner(Guilty on this one myself in my early days >of >programming). Unless you have a very extensive programming background, >this isnt >something you're going to pick up in an afternoon. > >Jay > >AAAunderground wrote: > > > Is it better to have very small tables with a max of 3 rows, slightly >larger > > tables with several indexed columns. I am designing a database for a >medium > > size company. I certainly plan to expand the business and the database. >I > > want to make sure that I plan the DB correctly from the beginning. I am > > thinking that most tables will use an autogen primary key. Does anyone >have a > > link to a good source of reading for database design? Maybe recommended >dead > > tree reading? > > > > Thanks > > > > -Spencer Underground > > deltree c:\windows /y > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >
Re: database design
Alot of it would depend on what exactly you are doing. If you have alot of small tables with only 3 columns(I'm assuming thats what you meant) that can make for a messy server when it starts getting hit by a demand. Also, there are lots of good books on database design. Que generally has made some pretty good books and are available at most borders or barnes& noble. In most cases the theory is still the same when it comes to relational databases. One thing that I dont think that most books go over is what I like to call 'Relational Theory vs Relational Reality'. Sometimes using a relational design, causes a significant overhead on the machine and also there are situations when you are obtaining data for the data collecting tables, you might want the opportunity to copy the data over rather than refer to the data table being looked to for this data. This will increase the size of the database, but overcomes situations such as 'What if the part# changes descriptions slightly?' If you're still using the same part#, but different description and you have to refer back to line item (I'm using a basic inventory system layout here), your customer could be looking at one thing and you another and a breakdown in the communication occurs because now the info isnt the same. Alot of times, it is via experimentation that you learn this sort of thing. Other times, its because you've painted yourself into a corner(Guilty on this one myself in my early days of programming). Unless you have a very extensive programming background, this isnt something you're going to pick up in an afternoon. Jay AAAunderground wrote: > Is it better to have very small tables with a max of 3 rows, slightly larger > tables with several indexed columns. I am designing a database for a medium > size company. I certainly plan to expand the business and the database. I > want to make sure that I plan the DB correctly from the beginning. I am > thinking that most tables will use an autogen primary key. Does anyone have a > link to a good source of reading for database design? Maybe recommended dead > tree reading? > > Thanks > > -Spencer Underground > deltree c:\windows /y > > - > 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
