database design

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=gcdmg-mysql-2@m.gmane.org

Re: database design

--00c09ffb4d2facad9904734c5f7c
Content-Type: text/plain; charset=ISO-8859-1

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.nanni@gmail.com
>
>


--
Claudio

--00c09ffb4d2facad9904734c5f7c--

Re: database design

--0016e64f6866c97d0b04734cada6
Content-Type: text/plain; charset=ISO-8859-1

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.nanni@gmail.com
> >
> >
>
>
> --
> Claudio
>

--0016e64f6866c97d0b04734cada6--

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 wr=
ote:
> 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 fro=
nt
> end to present your data. Your front end would request the user's name an=
d
> 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 =
=3D
> AT.Arcticle_types_id WHERE A.uid =3D
>
> hth,
> Arthur
>
> On Fri, Sep 11, 2009 at 8:22 AM, Claudio Nanni w=
rote:
>
>> 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) =A0select * from articles A left join article_types AT on A.article_t=
ype =3D
>> 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 direc=
ted
>> > to the best reading resources.
>> >
>> > as i begun building my database (as i went along), i now realise i hav=
e
>> 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. =
=A0so
>> 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=3Dclaudio.nanni@gmail.com
>> >
>> >
>>
>>
>> --
>> Claudio
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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.nanni@gmail.com
>>> >
>>> >
>>>
>>>
>>> --
>>> Claudio
>>>
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: database design

--00151774123e170cc004735cb739
Content-Type: text/plain; charset=ISO-8859-1

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.
>
>

--00151774123e170cc004735cb739--

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-normal ization.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.nanni@gmail.com
>>>> >
>>>> >
>>>>
>>>>
>>>> --
>>>> Claudio
>>>>
>>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@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=gcdmg-mysql-2@m.gmane.org

RE: database design

>-----Original Message-----
>From: AndrewJames [mailto:andrewhudds@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.nanni@gmail.com
>>>> >
>>>> >
>>>>
>>>>
>>>> --
>>>> Claudio
>>>>
>>>
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org