Entity Relationship Diagrams

[fractionMan]

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.

There is

The diagram shows that a given interview has many interviewers and that staff can be interviewers for many interviews.

"Interviewers" is an intermediate table that removes the many-many relationship. I'm not sure about you, but I tend to remove those as soon as possible.

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

Fair enough lol.

 

[fractionMan]

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

Yeah. It's awesome.

 

[untethered]

There is

The diagram shows that a given interview has many interviewers and that staff can be interviewers for many interviews.

"Interviewers" is an intermediate table that removes the many-many relationship. I'm not sure about you, but I tend to remove those as soon as possible.

You're right, I just misunderstood the purpose of the Interviewer table. This is indeed the join table between Employees and Interviews.

("Intermediate" tables are properly called join tables or junction tables where they exist purely to express a many-to-many relationship.)

 

[Onslow]

Yeah. It's awesome.

Drawing diagrams has never been so easy!

 

[Pingu]

You're right, I just misunderstood the purpose of the Interviewer table. This is indeed the join table between Employees and Interviews.

("Intermediate" tables are properly called join tables or junction tables where they exist purely to express a many-to-many relationship.)

this

many - many relationships in a PDM are bad bad bad and will only earn you a spanked botty (cos the potential for having duplicate values is high - this is a bad thing)


eta we work in data warehousing where this sort of thing is bad, it may not be considered so bad in OLTP or smaller systems but when you have several million rows that have to be deduped regularly then you REALLY dont want you DB design adding to the liklyhood of a duplicate value

 

[fractionMan]

And never ever do something like this:

Interview
----------
Date
Applicant_id
Interviewer1
Interviewer2
Interviewer3
Interviewer4

*shudders*

 

[Mainframeguy]

that diagram fractionman gave you is probably basically what they asked you for.

I used to do this for a living in a past life (among other things). Bit weird they are asking you to perform a data analyst activity, do you have to build a database next?

 

[Onslow]

I just gave them this.

They have an I.T TEAM!! for crying out loud, so if its wrong, I dont consider myself to blame

And after bumming around with this for so long, I doubt they are going to ask me to create a database

 

[Miss-Shelf]

we live in different universes
what are these entities you speak of?

 

[untethered]

I just gave them this.

They have an I.T TEAM!! for crying out loud, so if its wrong, I dont consider myself to blame

Pretty good! You need the Interview ID in the Interviewer table, though. What's the Recruiting Manager ID? Which table does it reference?

 

[Onslow]

Pretty good! You need the Interview ID in the Interviewer table, though. What's the Recruiting Manager ID? Which table does it reference?

Cheers

And yeh I agree about the interview ID. And I'm not quite sure as to why I created the RM ID I think it's surplus and I could of just used Employee number.

 

[cybertect]

And never ever do something like this:

Interview
----------
Date
Applicant_id
Interviewer1
Interviewer2
Interviewer3
Interviewer4

*shudders*

unless you're forced into it by Sage 200's limited import routines (from CSV files) that won't let you bring in a Sales Order Header separately from the Sales Order Lines, which may be assigned to different Nominal Codes in the General Ledger and VAT rates, so you have to collapse the subtotals of each and bring them in as a single transaction record

Sales Transaction
--------------------

Transaction_id
Ex_VAT_Price_Total
Nominal_Code_1
Nominal_Code_2
Nominal_Code_3
Nominal_Price_1
Nominal_Price_2
Nominal_Price_3
VAT_Rate_1
VAT_Rate_2
VAT_Rate_3
VAT_Value_1
VAT_Value_2
VAT_Value_3

etc.

[okay, I'm not storing my data like that, it's just an output ]

 

[DJWrongspeed]

That Gliffy thing is excellent, i was stuck at work without Visio and had to use Excel or Illustrator ? Gliffy came in very handy and also exports as .svg files for anyone else to edit later if Gliffy suddenly dies (hope not).

Cheers for the link.

 

[Wintermute]

oops