Can you check this ER Diagram for me please?

[awslc]

Hello there.

I am wondering whether you'd be able to check this ER diagram for me please for an online job service for university students? I have it constructed but I get anxious with these things and am looking for tips on whether my design is currently accurate.

http://www.daniweb.com/forums/attachment.php?attachmentid=18943&stc=1&d=1295818361

Thanks peeps!

~AWSLC

[debasisdas]

I really do not understand the link between student_apps and posted_jobs via status_messages.

[awslc]

I really do not understand the link between student_apps and posted_jobs via status_messages.

status_messages is just a table that holds many different pre-defined status'

Such as: Available, Rejected, Interview etc each with an _id that is then referenced in the posted_jobs and student_apps tables.

Hope that makes sense. Thank you :)

[debasisdas]

If that is a look table then there should not be any PK -- FK relationship between that table and other tables using values of that table.

[awslc]

If that is a look table then there should not be any PK -- FK relationship between that table and other tables using values of that table.

Sorry what do you mean by a look table?

I have modified my design, please see below:

http://www.daniweb.com/forums/attachment.php?attachmentid=18957&stc=1&d=1295871160

I'm still making small changes so please forgive any datatype inconsistencies, however your views on my new design are greatly appreciated!

Thank you very much

[debasisdas]

Sorry , my mistake.

Actually i wanted to say LOOK UP table.

That is a table to which other tables refer for data but with out any right relationship.

Better to keep that table as a standalone table.

[awslc]

Sorry , my mistake.

Actually i wanted to say LOOK UP table.

That is a table to which other tables refer for data but with out any right relationship.

Better to keep that table as a standalone table.

Hi there,

How would I implement this? I need the status_id to be kept in both jobs and application tables but I thought that there needed to be a relationship for this?

Would it literally be a status_id in both application and jobs tables and status_id in status table as a PK but no relationship between any tables?

Also what are your views on the implementation of the login table? Would you do this any better way?

Thanks for the help!

AWSLC

[debasisdas]

I suggest you store status_id in both jobs and application tables but without any rigid relationship.

Typically the look up table values are stored in 3 separate tables ,interdependent and independent from others.

for ex
1. CODELIST_NAME---what value it refers to (ex--country)
2. CODELIST_CODE---what are the possible code for the codelist (101,102,103,.... )
3. CODELIST_DECODE-decode vales for the actual codes (101--india,102--germany,103--usa ...and so on)

If implemented in your scenario, jobs and application tables will store the actual codes not the decode values.

[crfraser]

I suggest you store status_id in both jobs and application tables but without any rigid relationship.

Typically the look up table values are stored in 3 separate tables ,interdependent and independent from others.

for ex
1. CODELIST_NAME---what value it refers to (ex--country)
2. CODELIST_CODE---what are the possible code for the codelist (101,102,103,.... )
3. CODELIST_DECODE-decode vales for the actual codes (101--india,102--germany,103--usa ...and so on)

If implemented in your scenario, jobs and application tables will store the actual codes not the decode values.

I'm not sure I agree with the need for three tables. All that is needed in this case is a three/four column table:

Column one - the domain - e.g. 'APPLICATION_STATUS'
Column two - the code - e,g, 'APPLIED', 'ACKNOWLEDGED', 'PEND1', etc.
Column three - the meaning - e.g. 'Application tendered', 'Application Acknowledged', 'Pending First Interview', etc.

Column four (optional) - An expansion of the meaning (help text?)

Columns 1 and 2 form a unique key while any joins to other tables are made on the code and then conditioned on the domain.