p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: MySQL Database design help


Message #1 by Jon Bennett <jon@j...> on Tue, 18 Feb 2003 00:26:41 +0000
Hi,

I hope it's ok to post on this forum, even though I don't have the book 
(I got php4 web dev solutions, went a little over my head !).

I need some help on design a database (mysql) for a small recruitment 
site.

I'm thinking I need these tables:

1. Applicants
user_ID
firstName
lastName
email (used for username also)
password
address
postCode
tel
fax
cv - just a simple text version, not a link to a word doc or similar.

2. Vacancies
vacID
vacRef
vacDate
vacTitle
vacCatagoryID // I'm think this should be a foreign key linked to catID 
below
vacType
vacDescription
vacSalary
vacArea

3. Catagories
catID
catName
catDescription

4. Area
areaID
areaName
areaDescription


To be honest with you I get this far and then I'm lost. I've got Web 
Database Apps with MySQL and PHP from Orielly and I'm half way through, 
but I still don't understand how to go about creating a suitable DB for 
my needs. I've searched all over google and found mainly links to books 
(eps the one I've got !) and ain't getting nowhere. If anyone out there 
could give me some pointers / tips / clues I'd be really grateful.

Thanks in advance

Jon

--
jon bennett | new media designer | jon@j... | http://www.jben.net/
+44 (0) 1225 341039 | Trowbridge | Wiltshire | BA14 0AD | UK

Message #2 by "Gellings, C.O." <gellingsco@p...> on Tue, 18 Feb 2003 10:09:41 +0100
Hi,
Your tables' setup look fine to me. Now you need tables where you link you 
data-tables with the id-fields you've got. Like which vacancies are in 
which category and in which area

table open_applications
vacID
catID
areaID

you may even want a table for applicants which applied to a vacancy

further more, try Beginning PHP from WROX. it's good to start off with, you 
can turn to the O'Reilly one later on

good luck

Carl

Message #3 by "David Scott-Bigsby" <DScott-Bigsby@P...> on Tue, 18 Feb 2003 09:00:53 -0800
Jon,

It's pretty hard to say if you've got a data model that's appropriate 
for your needs, since we don't know what your business & functional 
requirements are.

The usual process for creating a data model (which you then instantiate 
with tables in a database system like MySQL) is to go through the forms 
and reports you'll be using, make a big list of every data item, and 
then "normalize" that data through 3 or 4 steps into seperate entities 
-- your resulting tables.

This process is beyond what I'm willing to try to explain here, but 
fortunately, there's lots of tutorials on the web for exactly this. They 
won't compare to a good course on data modelling (well worth the 
investment -- your typical programming job is to create a front-end to a 
database), but they're free and immediate.

Searching Google for "database normalization tutorial" turned up lots of 
links, including:

* 
http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=3D95
* http://www.devshed.com/Server_Side/MySQL/Normal/Normal1/page1.html
* http://www.sqlmag.com/Articles/Index.cfm?ArticleID=3D4887&pg=3D1

Good luck!

dsb

***************************************       
David Scott-Bigsby
Product Manager, Web Site and PEDN

PureEdge Solutions
The Leader in Secure XML e-Forms

v:250-708-8145  f:250-708-8010
1-888-517-2675   www.PureEdge.com
***************************************


> -----Original Message-----
> From: Jon Bennett [mailto:jon@j...]
> Sent: Monday, February 17, 2003 4:27 PM
> To: beginning php
> Subject: [beginning_php] MySQL Database design help
>
>
> Hi,
>
> I hope it's ok to post on this forum, even though I don't
> have the book
> (I got php4 web dev solutions, went a little over my head !).
>
> I need some help on design a database (mysql) for a small recruitment
> site.
>
> I'm thinking I need these tables:
>
> 1. Applicants
> user_ID
> firstName
> lastName
> email (used for username also)
> password
> address
> postCode
> tel
> fax
> cv - just a simple text version, not a link to a word doc or similar.
>
> 2. Vacancies
> vacID
> vacRef
> vacDate
> vacTitle
> vacCatagoryID // I'm think this should be a foreign key
> linked to catID
> below
> vacType
> vacDescription
> vacSalary
> vacArea
>
> 3. Catagories
> catID
> catName
> catDescription
>
> 4. Area
> areaID
> areaName
> areaDescription
>
>
> To be honest with you I get this far and then I'm lost. I've got Web
> Database Apps with MySQL and PHP from Orielly and I'm half
> way through,
> but I still don't understand how to go about creating a
> suitable DB for
> my needs. I've searched all over google and found mainly
> links to books
> (eps the one I've got !) and ain't getting nowhere. If anyone
> out there
> could give me some pointers / tips / clues I'd be really grateful.
>
> Thanks in advance
>
> Jon
>
> --
> jon bennett | new media designer | jon@j... | http://www.jben.net/
> +44 (0) 1225 341039 | Trowbridge | Wiltshire | BA14 0AD | UK
>
>
>
Message #4 by Jon Bennett <jon@j...> on Thu, 20 Feb 2003 23:54:43 +0000
Hi Carl,

Thanks for the reply (and sorry for the delay in mine!).

I found a tute on www.sitepoint.com which is along the lines of what I 
need. I think getting the php / mysql right shouldn't be too hard, I 
just want to be sure that I;ve got the DB right.

I guess you're talking about foreign keys ?

Jon

On Tuesday, February 18, 2003, at 09:09 AM, Gellings, C.O. wrote:

> Hi,
> Your tables' setup look fine to me. Now you need tables where you link 
> you data-tables with the id-fields you've got. Like which vacancies 
> are in which category and in which area
>
> table open_applications
> vacID
> catID
> areaID
>
> you may even want a table for applicants which applied to a vacancy
>
> further more, try Beginning PHP from WROX. it's good to start off 
> with, you can turn to the O'Reilly one later on
>
> good luck
>
> Carl
>
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to 
>

Message #5 by Jon Bennett <jon@j...> on Thu, 20 Feb 2003 23:56:47 +0000
Hi David,

That's what I've been looking for. I've been reading things on 
phpbuilder.com etc on this topic and I'll be sure to check out those 
links, thanks.

It's late so I won' give you a complete business model, but I'll try to 
post something tomorrow.

Thanks,

Jon

On Tuesday, February 18, 2003, at 05:00 PM, David Scott-Bigsby wrote:

> Jon,
>
> It's pretty hard to say if you've got a data model that's appropriate 
> for your needs, since we don't know what your business & functional 
> requirements are.
>
> The usual process for creating a data model (which you then 
> instantiate with tables in a database system like MySQL) is to go 
> through the forms and reports you'll be using, make a big list of 
> every data item, and then "normalize" that data through 3 or 4 steps 
> into seperate entities -- your resulting tables.
>
> This process is beyond what I'm willing to try to explain here, but 
> fortunately, there's lots of tutorials on the web for exactly this. 
> They won't compare to a good course on data modelling (well worth the 
> investment -- your typical programming job is to create a front-end to 
> a database), but they're free and immediate.
>
> Searching Google for "database normalization tutorial" turned up lots 
> of links, including:
>
> * http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=95
> * http://www.devshed.com/Server_Side/MySQL/Normal/Normal1/page1.html
> * http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1
>
> Good luck!
>
> dsb
>
> ***************************************
> David Scott-Bigsby
> Product Manager, Web Site and PEDN
>
> PureEdge Solutions
> The Leader in Secure XML e-Forms
>
> v:250-708-8145  f:250-708-8010
> 1-888-517-2675   www.PureEdge.com
> ***************************************
>
>
>> -----Original Message-----
>> From: Jon Bennett [mailto:jon@j...]
>> Sent: Monday, February 17, 2003 4:27 PM
>> To: beginning php
>> Subject: [beginning_php] MySQL Database design help
>>
>>
>> Hi,
>>
>> I hope it's ok to post on this forum, even though I don't
>> have the book
>> (I got php4 web dev solutions, went a little over my head !).
>>
>> I need some help on design a database (mysql) for a small recruitment
>> site.
>>
>> I'm thinking I need these tables:
>>
>> 1. Applicants
>> user_ID
>> firstName
>> lastName
>> email (used for username also)
>> password
>> address
>> postCode
>> tel
>> fax
>> cv - just a simple text version, not a link to a word doc or similar.
>>
>> 2. Vacancies
>> vacID
>> vacRef
>> vacDate
>> vacTitle
>> vacCatagoryID // I'm think this should be a foreign key
>> linked to catID
>> below
>> vacType
>> vacDescription
>> vacSalary
>> vacArea
>>
>> 3. Catagories
>> catID
>> catName
>> catDescription
>>
>> 4. Area
>> areaID
>> areaName
>> areaDescription
>>
>>
>> To be honest with you I get this far and then I'm lost. I've got Web
>> Database Apps with MySQL and PHP from Orielly and I'm half
>> way through,
>> but I still don't understand how to go about creating a
>> suitable DB for
>> my needs. I've searched all over google and found mainly
>> links to books
>> (eps the one I've got !) and ain't getting nowhere. If anyone
>> out there
>> could give me some pointers / tips / clues I'd be really grateful.
>>
>> Thanks in advance
>>
>> Jon
>>
>> --
>> jon bennett | new media designer | jon@j... | http://www.jben.net/
>> +44 (0) 1225 341039 | Trowbridge | Wiltshire | BA14 0AD | UK
>>
>>
>>
>
>
>

--
jon bennett | new media designer | jon@j... | http://www.jben.net/
+44 (0) 1225 341039 | Trowbridge | Wiltshire | BA14 0AD | UK

Message #6 by "David Scott-Bigsby" <DScott-Bigsby@P...> on Fri, 21 Feb 2003 10:01:40 -0800
> It's late so I won' give you a complete business model, but
> I'll try to post something tomorrow.

Um, that's okay.

I was trying to make the point that one needs to know the business model 
in order to create the data model, and that we on this list couldn't 
really do that for you. I mean, we could whip up a little data model, 
but the chance of us misunderstanding your requirements -- and you 
assuming we're experts -- would be pretty high. If you know what your 
system is supposed to do, then with a bit of reading  ceate a data model 
and then *validate* it against the operations you'd like to perform on 
it, I'm sure you'll do a fine job.

Of course, if you have any fairly specific questions, feel free to post 
them.

dsb

***************************************       
David Scott-Bigsby
Product Manager, Web Site and PEDN

PureEdge Solutions
The Leader in Secure XML e-Forms

v:250-708-8145  f:250-708-8010
1-888-517-2675   www.PureEdge.com
***************************************

  Return to Index