List:General Discussion« Previous MessageNext Message »
From:AndrewJames Date:September 12 2009 5:19am
Subject:Re: database design
View as plain text  
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" <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
>>>
>>
> 
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