Entity Relationship Diagrams

[Pingu]

pay us to come in and re-engineer the whole process

we would start with an in-depth analysis of the business needs and desires. Hold loads of meetings. Present a very slick report outlining several options and then build the database, train the users etc etc.

seeing as its you we could probably do it for about 50k ish?

 

[mrs quoad]

seeing as its you we could probably do it for about 50k ish?

Shit.

Er.

About my previous post.

Make it £300k

small print ftw!

 

[Onslow]

its kind of sinking in.

A RECRUITING MANAGER authorises a JOB VACANCY. A JOB VACANCY is assigned a RECRUITMENT CLERK. An APPLICANT applies to a JOB VACANCY.

which is fine, but then theres alot more to a recruitment process than that.

manager short lists applicant, applicants come for interview, successful applicants get conditional offer. recruitment clerk organises CRB Check, References, and Medical, Results from them come back, then applicant is offered unconditional offer.
pfffffff

 

[Onslow]

I'll do you one for £300, Onslow

ohhh, get some new material!!

 

[Pingu]

Shit.

Er.

About my previous post.

Make it £300k

small print ftw!

yes but you are clearly not adopting a fully consultative approach.

it will all end in tears I tell you.



on a serious note 50k for a full on process re-engineering and solution design is cheap but its probably not what you need

 

[Shippou-Sensei]

its kind of sinking in.

A RECRUITING MANAGER authorises a JOB VACANCY. A JOB VACANCY is assigned a RECRUITMENT CLERK. An APPLICANT applies to a JOB VACANCY.

which is fine, but then theres alot more to a recruitment process than that.

manager short lists applicant, applicants come for interview, successful applicants get conditional offer. recruitment clerk organises CRB Check, References, and Medical, Results from them come back, then applicant is offered unconditional offer.
pfffffff

i think it's the next stage movoing away from processes onto seeing colections of data is key

i think... it's been about 3 years since i have done an ERD mind you

i'm more used to working from a kinda bottom up perspective now identifying data that needs to be stored and then forming logical collections and then their relations

i need a refresher course

 

[kained&able]

your wrong. An applicant can apply for many jobs.

I am currently applying for 7 jobs with the same agency using two different recruitment consultants as well so have fun with that!(its actualy easy to resolve but god i hate this shite)

Onslow most of "manager short lists applicant, applicants come for interview, successful applicants get conditional offer. recruitment clerk organises CRB Check, References, and Medical, Results from them come back, then applicant is offered unconditional offer."

arent entities. ignore them for the moment. The only entities you've listed there are manager, applicants, recruitment clerk and possibly the CRB and referees are external entities.

I think.

First job is to work out all the entities. and put them in big boxes on your page and link whomever needs to talk to each other.(dont bother trying to get rid of all the many to many relationships yet it will confuse everything)

Then work out where the data is stored.(and do primary and forign keys)

Then normalise everything(get rid of many to many relationships and add the processes and all sorts of fun) a few times cry a lot and tell your employees to fuck themsleves and get someone in to it.




dave

 

[mrs quoad]

yes but you are clearly not adopting a fully consultative approach.

it will all end in tears I tell you.

Hence the new small print. He pays me, I pay you, and still have £250k left over

Which sounds like a good thing all round.

 

[Onslow]

your wrong. An applicant can apply for many jobs.

I am currently applying for 7 jobs with the same agency using two different recruitment consultants as well so have fun with that!(its actualy easy to resolve but god i hate this shite)

Onslow most of "manager short lists applicant, applicants come for interview, successful applicants get conditional offer. recruitment clerk organises CRB Check, References, and Medical, Results from them come back, then applicant is offered unconditional offer."

arent entities. ignore them for the moment. The only entities you've listed there are manager, applicants, recruitment clerk and possibly the CRB and referees are external entities.

I think.

First job is to work out all the entities. and put them in big boxes on your page.

dave

Cheers, and the key identifies for those entities i presume would be job reference number, applicant number, clerks name, mangers name.

and the possible external entities would be crb number and reference names.

Im guessing

and your right about the applicant part. although i knew that, but obv didnt show it.

there must be more than those entities though...

 

[Pingu]

Hence the new small print. He pays me, I pay you, and still have £250k left over

Which sounds like a good thing all round.

I may have spotted a teeny weeny flaw in your logic (well the pre edit logic anyhow)

scope creep ftw

 

[kained&able]

Cheers, and the key identifies for those entities i presume would be job reference number, applicant number, clerks name, mangers name.

and the possible external entities would be crb number and reference names.

Im guessing

and your right about the applicant part. although i knew that, but obv didnt show it.

there must be more than those entities though...

Primary/Forign keys must be unuiqe. So yeah things like job number, staff number(or NI number) managers name cant be used as a key as there could at some point in the future be two John smiths working for you or something. Which would then crash your database.

Doubt you'd have to bother putting down referees name(and name isnt a inuque key anyway) to be honest. Just a box saying referees with a relationship and a process should surfice but dont hold me to it. CRB i'm not sure but crb number would make sense if the data is needed somewhere else for something if not probbaley not needed either.

dave

 

[untethered]

Primary/Forign keys must be unuiqe. So yeah things like job number, staff number(or NI number) managers name cant be used as a key as there could at some point in the future be two John smiths working for you or something. Which would then crash your database.

True, but I would make a rule to always use an arbitrary numeric primary key for every table. Do not use things like NI numbers -- you are creating a dependency on another's system which you can't guarantee will be maintained consistently. Also, depending on the design of your database/software, there may be privacy issues.

 

[untethered]

your wrong. An applicant can apply for many jobs.

I am currently applying for 7 jobs with the same agency using two different recruitment consultants as well so have fun with that!(its actualy easy to resolve but god i hate this s-)

Applications
-----------
id
job_id
candidate_id
consultant_id
...

Very easy.

 

[untethered]

An applicant can apply for one job, but a job can have many applicants. A recruiting manager can be recuiting to many jobs, but a job may only have one recruiting manager. A recruitment clerk can be sorting out many jobs, for many managers and many applicants! but each of those can only have one recruitment clerk.

As alluded to above, the key to all this is realising that an Application is an entity.

 

[kained&able]

yeah i agree staff nmumber is definatly the best option for a staff table. But if for some reason it doesn't exist or cant exist then its not like NI nukbers are going to hcnage anytime soon.

but your right.



I'm well shocked i can remmeber any of this! Oh and if unthered and me disagree at any point go with him. I think he knows/remmebers more then me.


dave

 

[untethered]

yeah i agree staff nmumber is definatly the best option for a staff table. But if for some reason it doesn't exist or cant exist then its not like NI nukbers are going to hcnage anytime soon.

Create an arbitrary numeric ID for the staff table and hey presto, instant staff IDs.

"It's not like x is going to change any time soon."

Things do change, you'd be surprised. Look at ISBNs, for example.

Trying to be clever with primary keys is a world-class way to ruin a database. Just make them numeric, arbitrary and of course unique and you'll be fine.

 

[untethered]

If you want to show us what you're doing, put it up on Gliffy:

http://www.gliffy.com/

 

[kained&able]

Second i wrote it i started thinking i wonder if the ID cards will change things

fair.


dave

 

[untethered]

Remember this technique?

Go and write a description of the system. Then underline all the nouns.

All the nouns are potentially entities. Not just the obvious ones like Job, Applicant and Consultant but also Referee, Interview, Application, etc.

 

[Onslow]

.

All the nouns are potentially entities. Not just the obvious ones like Job, Applicant and Consultant but also Referee, Interview, Application, etc.

So the referee and interview ARE entities

And when you say application. You dont mean applicant, you mean the process of this person applying for the job?

An applicant has a unique idenitfy and so does each particular job, how does 'application' fit in? thanks in advance.

 

[Pingu]

what dbms is being used btw as this will help us understand what limitations etc are presented by the system

 

[kained&able]

I say interview isn't an entity its a process.

referee is an entity but i'd be shocked if you neeed a referee table or anything so just the box should do no need for keys or anything. It might even be illegal to keep referees information on the system under data protection act but i'm not sure.

You dont mean applicant, you mean the process.....

A process is not an entity.


dave

 

[fractionMan]

Interviews are entities if you need to record details about them (such as date, outcome and attendees)

 

[untethered]

So the referee and interview ARE entities

There isn't always a clear-cut solution to whether something should be treated as a separate entity in a database but any "thing" is a potential entity.

A good way to tell is whether there is a one-to-many relationship between any entity in your system and any potential entity.

For example,

- Person
- NI number

Each of these is a "thing" and therefore, potentially an entity. But every NI number belongs to just one Person (a one-to-one relationship) and therefore the NI number might as well be rolled into Person (as an attribute) rather than treated as a separate entity.

Consider:

- Applicant
- Interview

Again, common sense tells us that an Applicant and an Interview are different things, but also that an Applicant could have more than one Interview. So make each of these a separate entity.

An Interview belongs to an Application. An Application can have many Interviews.

Interview
---------
id (PK)
application_id (FK)
...

Application
-----------
id (PK)
...

And when you say application. You dont mean applicant, you mean the process of this person applying for the job?

Correct. Life is also easier in these discussions if you use capital letters for entities.

An applicant has a unique idenitfy and so does each particular job, how does 'application' fit in? thanks in advance.

An Application links Applicants (or Candidates, might be a better name) and Jobs.

Application
-----------
id
job_id
candidate_id
...

Every Application has just one Job and one Candidate. But a Job may have many Applications and through that, many Candidates. Likewise, a Candidate may have many Applications and through that, many (potential) Jobs.

 

[kained&able]

shit, of course!

right i'm off before i confuse the situation more.

Good luck onslow.


dave

 

[Onslow]

There isn't always a clear-cut solution to whether something should be treated as a separate entity in a database but any "thing" is a potential entity.

A good way to tell is whether there is a one-to-many relationship between any entity in your system and any potential entity.

For example,

- Person
- NI number

Each of these is a "thing" and therefore, potentially an entity. But every NI number belongs to just one Person (a one-to-one relationship) and therefore the NI number might as well be rolled into Person (as an attribute) rather than treated as a separate entity.

Consider:

- Applicant
- Interview

Again, common sense tells us that an Applicant and an Interview are different things, but also that an Applicant could have more than one Interview. So make each of these a separate entity.

An Interview belongs to an Application. An Application can have many Interviews.

Interview
---------
id (PK)
application_id (FK)
...

Application
-----------
id (PK)
...




Correct. Life is also easier in these discussions if you use capital letters for entities.



An Application links Applicants (or Candidates, might be a better name) and Jobs.

Application
-----------
id
job_id
candidate_id
...

Every Application has just one Job and one Candidate. But a Job may have many Applications and through that, many Candidates. Likewise, a Candidate may have many Applications and through that, many (potential) Jobs.

And with that, a light has been switched inside my skull. I shall crack on and see how far I get before it dims.

oh and cheers Dave

 

[untethered]

I say interview isn't an entity its a process.

referee is an entity but i'd be shocked if you neeed a referee table or anything so just the box should do no need for keys or anything. It might even be illegal to keep referees information on the system under data protection act but i'm not sure.

A process is not an entity.

An ERD (and by extension, a database) stores information about things. It cannot tell you about processes in the sense of describing the steps in a process or the sequence between processes. Other diagrams do that (eg. flowcharts, UML state charts).

But a process can be a thing just like any tangible thing and therefore an entity in a database.

Imagine a football database.

"There are a number of teams in the league. Each team has many players. The teams play matches against each other, for which they are awarded points. The team with the most points at the end of the season wins the league."

Underline the nouns. Those are your potential entities. A Match is clearly a process rather than a tangible object, but it's still an entity.

 

[fractionMan]

Like this:

(that gliffy is pretty damn good)

 

[Onslow]

Like this:

(that gliffy is pretty damn good)

FractionMan, did you just create that on Gliffy?!?

 

[untethered]

Like this:

(that gliffy is pretty damn good)

There could be many Interviewers in each Interview, so that needs to be a many-to-many.

Interviewer is really just a role that a member of Staff plays. They are not separate things, so Staff can be linked directly to the join table of the many-to-many between Staff and Interview.

On naming, one would expect a Staff table to hold Staffs. It doesn't. It holds Employees.

Your use of Vacancy is better than mine of Job. One Job (or Role) will in time generate many Vacancies, every time that it needs to be recruited for.