Member Avatar for 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.

Thanks peeps!

~AWSLC

database_design.png 65.91 KB
  • 3 Contributors
  • 8 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by crfraser

Dani AI

Generated

Good ERDs for a job board keep status as an attribute of the entity, not as a bridge between entities. In your case, a student application belongs to a job and to a student; both jobs and applications can each have their own status. The extra hop via status_messages is what confused . Keep the relationship from student_apps directly to posted_jobs, and store a status attribute on each table.

On lookups: you can model codes a few ways. A simple, flexible approach (agreeing with ) is one table keyed by (domain, code) so you can keep job and application statuses in the same table but in separate domains. Whatever you choose, do enforce foreign keys so you never store a code that does not exist; that is exactly what FK constraints are for and it preserves referential integrity. (postgresql.org)

Example DDL sketch you can adapt:

CREATE TABLE status_codes (
  domain TEXT NOT NULL,
  code   TEXT NOT NULL,
  label  TEXT NOT NULL,
  PRIMARY KEY (domain, code)
);

-- jobs
ALTER TABLE posted_jobs
  ADD COLUMN status_domain TEXT NOT NULL DEFAULT 'JOB_STATUS',
  ADD COLUMN status_code   TEXT NOT NULL,
  ADD CONSTRAINT fk_job_status
    FOREIGN KEY (status_domain, status_code)
    REFERENCES status_codes(domain, code);

-- applications
ALTER TABLE student_apps
  ADD COLUMN status_domain TEXT NOT NULL DEFAULT 'APPLICATION_STATUS',
  ADD COLUMN status_code   TEXT NOT NULL,
  ADD CONSTRAINT fk_app_status
    FOREIGN KEY (status_domain, status_code)
    REFERENCES status_codes(domain, code);

If you need to track changes over time, add an application_status_history table with (application_id, changed_at, domain, code, changed_by). If your status list is truly static, some RDBMS offer enums, but they are harder to change later (e.g., removing or reordering values), so a lookup table is usually safer. (postgresql.org)

Login: use a single users table with a salted password hash (e.g., bcrypt/argon2), plus role tables for Student/Employer keyed to users.user_id. Avoid storing plaintext passwords.

Recommended Answers

All 8 Replies

Member Avatar for debasisdas

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

Member Avatar for 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 :)

Member Avatar for 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.

Member Avatar for 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:

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

database_design(2).png 61.73 KB
Member Avatar for 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.

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.