>-----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" <kykim...@gmail.com> >Sent: Saturday, September 12, 2009 8:22 AM >To: "Arthur Fuller" <fuller.art...@gmail.com> >Cc: "Claudio Nanni" <claudio.na...@gmail.com>; "AndrewJames" ><andrewhu...@gmail.com>; "mysql" <mysql@lists.mysql.com> >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 <fuller.art...@gmail.com> >> 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 = <insert your variable here> >>> >>> hth, >>> Arthur >>> >>> On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni >>> <claudio.na...@gmail.com>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 <andrewhu...@gmail.com> >>>> >>>> > 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
