If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to implement JobTitle:JobPosting Relationship?
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 |
#2
|
|||
|
|||
On Thu, 1 Sep 2005 12:57:17 -0600, "Siegfried Heintze"
wrote: 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] |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
On Thu, 1 Sep 2005 23:13:12 -0600, "Siegfried Heintze"
wrote: 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? 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] |
#5
|
|||
|
|||
"Siegfried Heintze" wrote in news:e1mXE03rFHA.260
@TK2MSFTNGP11.phx.gbl: 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 |
#6
|
|||
|
|||
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. 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 |
#7
|
|||
|
|||
On Fri, 2 Sep 2005 14:02:27 -0600, "Siegfried Heintze"
wrote: 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] |
#8
|
|||
|
|||
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 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] |
#9
|
|||
|
|||
On Sun, 4 Sep 2005 16:56:40 -0600, "Siegfried Heintze"
wrote: 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] |
#10
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relationship feature/bug/accident | Peter Danes | General Discussion | 22 | September 11th, 2005 11:15 PM |
Relationship feature/bug/accident | Peter Danes | Using Forms | 22 | September 11th, 2005 11:15 PM |
Impossible? Relationship / Join Quandary | SteveTyco | Database Design | 1 | May 5th, 2005 01:58 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |