List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:September 14 2009 2:10pm
Subject:RE: database design
View as plain text  
>-----Original Message-----
>From: AndrewJames [mailto:andrewhudds@stripped]
>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" <kykimdba@stripped>
>Sent: Saturday, September 12, 2009 8:22 AM
>To: "Arthur Fuller" <fuller.artful@stripped>
>Cc: "Claudio Nanni" <claudio.nanni@stripped>; "AndrewJames"
><andrewhudds@stripped>; "mysql" <mysql@stripped>
>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.artful@stripped>
>> 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.nanni@stripped>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 <andrewhudds@stripped>
>>>>
>>>> > 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=1
>>>> >
>>>> >
>>>>
>>>>
>>>> --
>>>> Claudio
>>>>
>>>
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




Thread
database designAndrewJames11 Sep
  • Re: database designClaudio Nanni11 Sep
    • Re: database designArthur Fuller11 Sep
      • Re: database designKyong Kim12 Sep
        • Re: database designAndrewJames12 Sep
          • Re: database designArthur Fuller12 Sep
          • Re: database designMogens Melander14 Sep
          • RE: database designJerry Schwartz14 Sep