How to implement JobTitle:JobPosting Relationship?

[Siegfried Heintze]

I want to establish a one to many relationship where multiple JobPostings
share a common JobTitle. This is to implement the user interface where the
user can scroll thru a list of job titles, select a job title and find all
the postings with that job title.

The JobTitle relationship will consist of only two columns: an auto
increment integer field and a field called sName that will store the title
as a VARCHAR(255).

Assuming all the job titles are going to be unique and the auto increment
field is also unique:

Question #1:
Which would you make the primary key? The integer "id" field, or the sName
field, or both? Why?

Would you index the other field too (assuming you did not say both)?

If you are creating new jobs, (perhaps you are scraping them from dice or
hot jobs), you conistantly are looking up job titles to see if they exists
and if so, what is their unique integer id whose value is to be stored in
the fkJobTitle field in the JobPosting relationship.

Question #2:
How would you implement the lookup operation that you would use when
creating a new job posting with a (possibly new and unique) job title? Would
you try an SQL "INSERT" and then wait for an error when that job title is
already there? Or would you use an SQL "SELECT" and then, if the result set
was empty use an "INSERT"?

Thanks,
Siegfried

 

[John Vinson]

I want to establish a one to many relationship where multiple JobPostings
share a common JobTitle. This is to implement the user interface where the
user can scroll thru a list of job titles, select a job title and find all
the postings with that job title.

The JobTitle relationship will consist of only two columns: an auto
increment integer field and a field called sName that will store the title
as a VARCHAR(255).

Assuming all the job titles are going to be unique and the auto increment
field is also unique:

Question #1:
Which would you make the primary key? The integer "id" field, or the sName
field, or both? Why?

The Autonumber ID field; because it is unique, short, and stable. The
Job Title might change over time - though you can use Cascade Updates
if necessary to propagate this change, that adds complexity and some
system dependency. You certainly would not use both - each one is
unique in its own right.

If you were to want to use the JobTitle as the PK, do away with the
autonumber completely - just use a one-field table with the title (and
put cascade updates on all relationships to the Text(255) foreign key
JobTitle fields).

Would you index the other field too (assuming you did not say both)?

If you want it unique, put a (separate) unique Index on it.

If you are creating new jobs, (perhaps you are scraping them from dice or
hot jobs), you conistantly are looking up job titles to see if they exists
and if so, what is their unique integer id whose value is to be stored in
the fkJobTitle field in the JobPosting relationship.

You should never need to see or type or use the fkJobTitle integer,
unless you're doing debugging. You'ld have a Form with a Combo Box,
which would have the numeric ID as its bound column, but which would
display the text title.

Question #2:
How would you implement the lookup operation that you would use when
creating a new job posting with a (possibly new and unique) job title? Would
you try an SQL "INSERT" and then wait for an error when that job title is
already there? Or would you use an SQL "SELECT" and then, if the result set
was empty use an "INSERT"?

Two options: have a simple maintenance form - which needn't even
display the Autonumber field, though it could; just a continuous form
displaying all the titles would be very easy to implement and to use.
Sort its Recordsource query alphabetically by title.

Or, use VBA code in the NotInList event of the form Combo Box used to
enter titles. Search http://www.mvps.org/access for Not In List for
sample code.


John W. Vinson[MVP]

 

[Siegfried Heintze]

John,

If you were to want to use the JobTitle as the PK, do away with the
autonumber completely - just use a one-field table with the title (and
put cascade updates on all relationships to the Text(255) foreign key
JobTitle fields).

Hmmm.... if you were storing many thousands of jobs, would not this be
pretty space inefficient?

The job posting would be storing the job title and it would be stored
redundantly in the single column relation.

Thanks,
Siegfried

 

[John Vinson]

John,


Hmmm.... if you were storing many thousands of jobs, would not this be
pretty space inefficient?

Sure. Disk is cheap, though. The bigger cost would be that a 256-byte
index entry is going to make for slower queries than a 4-byte Long
Integer entry; that, and the cascading updates. Also, it might push
your employee table closer to the hard limit of 2000 bytes actually
occupied per record.

The job posting would be storing the job title and it would be stored
redundantly in the single column relation.

Not really redundant; you have a one (JobTitle) to many (workers)
relationship, and the JobTitle is a valid, real-life entity which
deserves a table of its own.

I'd go with the two field table; use the Autonumber as the primary key
and a Long Integer foreign key, and uniquely index the job title in
the JobTitles table.

John W. Vinson[MVP]

 

[Tim Ferguson]

The job posting would be storing the job title and it would be stored
redundantly in the single column relation.

Don't confuse logical design with physical, and most certainly don't use
assumptions about physical implementation to cripple the logical design.

In this example (AIUI) the entire list of FK text keys would be held in a
single index b-table, and there would be no storage at all of the "table"
itself. In other words, a single list of text values saves a bucket load of
disk space over a two-column number & text, which would have to be stored
as a table. But that is no reason for picking the design either.

Just a thought...


Tim F

 

[Siegfried Heintze]

Question #2:

Two options: have a simple maintenance form - which needn't even
display the Autonumber field, though it could; just a continuous form
displaying all the titles would be very easy to implement and to use.
Sort its Recordsource query alphabetically by title.

Or, use VBA code in the NotInList event of the form Combo Box used to
enter titles. Search http://www.mvps.org/access for Not In List for
sample code.

OK -- that makes sense for user interfaces. But what if I am scraping
information from the internet and there is no user interface. Let's suppose
many job postings can share the same job title and I have 1000000 job
postings and 40000 job titles. When creating a new job posting, I need the
integer value for the fkJobTitle. I don't know if the fkJobTitle even
exists. I can assume it does not and try to SQL "INSERT" (followed by SQL
"SELECT MAX) or assume it does exist and SQL "SELECT". Which is more
efficient? Are there other more efficient options?

Thanks,
Siegfried

 

[John Vinson]

OK -- that makes sense for user interfaces. But what if I am scraping
information from the internet and there is no user interface. Let's suppose
many job postings can share the same job title and I have 1000000 job
postings and 40000 job titles. When creating a new job posting, I need the
integer value for the fkJobTitle. I don't know if the fkJobTitle even
exists. I can assume it does not and try to SQL "INSERT" (followed by SQL
"SELECT MAX) or assume it does exist and SQL "SELECT". Which is more
efficient? Are there other more efficient options?

You can use a SQL INSERT joining to the JobTitle table *by the job
title text*, to pick up exact matches:

strSQL = "INSERT INTO tablename(JobTitleID)
SELECT JobTitleID FROM JobTitles
WHERE JobTitle = '" & strJobTitle & "';"

and execute this query...

John W. Vinson[MVP]

 

[Siegfried Heintze]

How will this SQL statement (below) behave if there is no such JobTitle?
For this case I need to add a new row to the job title table and store the
newly created JobTitleID (as you call it) into the JobPosting table. Is
there an efficient way to do this?

As far as I can determine, I'm stuck with using SELECT and then (if the
SELECT failed to find any) INSERT or INSERT and then (if the INSERT fails
because of duplicates are not allowed) SELECT.

Either way, the worst case scenerio requires JET to perform two redundant
BTREE lookups. Does this seem silly to you? Is there not a better option?

Thanks,
Siegfried

 

[John Vinson]

How will this SQL statement (below) behave if there is no such JobTitle?
For this case I need to add a new row to the job title table and store the
newly created JobTitleID (as you call it) into the JobPosting table. Is
there an efficient way to do this?

As far as I can determine, I'm stuck with using SELECT and then (if the
SELECT failed to find any) INSERT or INSERT and then (if the INSERT fails
because of duplicates are not allowed) SELECT.

Either way, the worst case scenerio requires JET to perform two redundant
BTREE lookups. Does this seem silly to you? Is there not a better option?

Well, it's not - strictly speaking - redundant, IMO. You're doing two
distinct operations. What you can do is a "Frustrated Outer Join"
query to append only the new records; create a query joining the
linked input table to the JobTitle table, using a Left Outer Join on
JobTitle and a criterion of IS NULL to exclude those records which DO
have a value already.

John W. Vinson[MVP]

 

[Siegfried Heintze]

Well, it's not - strictly speaking - redundant, IMO. You're doing two
distinct operations. What you can do is a "Frustrated Outer Join"
query to append only the new records; create a query joining the
linked input table to the JobTitle table, using a Left Outer Join on
JobTitle and a criterion of IS NULL to exclude those records which DO
have a value already.

This is a new one on me! Do you have any SQL example statements or a URL?

Thanks,
Siegfried