Primarykey Foreignkey relationships
-
I have created a database where an employer coop program hires students. I came up with 4 tables employerjobannouncement(the jobs that are listed), employerparticipant(the employer who wants to announce jobs), studentapplication(The app the student fills in their qualifications),and studentparticipant (the students who are picked).
I am having difficulties defing the keys between the tables. I know Jobid probly would be unique for the primary key but can someone help me with the foreign key?
here is a pic of the tables
-
Are these the only 4 tables you have in the DB?
The design isn't logical to me, your tables do not appear to be normalized.
MCITP SQL 2005, MCSA SQL 2012
-
This pretty obviously homework. Did this structure come from your professor or did you design it? What is the actual assignment?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
yes I wont lie to you it is homework. I was given a task to design a employer student coop database where an employer s match a student with a job. I designed this and put the tables. I am having trouble with what should be my primary and foreign keys. Im trying to read about I was figuring maybe Employerid studentid. Im not asking for the answer just a little steer in the right direction
-
Yes these are the only tables that I had to make
-
Personally I would have normalised this design further with separate tables for Employer, student, job, Job Applies, plus others. Is that an option rather than have it designed how you have done so far?
MCITP SQL 2005, MCSA SQL 2012
-
Sean Lange (4/29/2015)
This pretty obviously homework. Did this structure come from your professor or did you design it? What is the actual assignment?Your tables are severely lacking in normalization. You are storing all sorts of data in the wrong place. I am guessing that what you are trying to do is have a list of jobs and any number of students can be assigned these jobs? Start at the bottom and work in the details. What entities are involved here? Employers and Students right? Where are those tables? Move all the demographic stuff to the physical entity tables and NOT in the detail tables.
Then each employer can list multiple jobs? So you need a job listing table.
Each student can have any number of jobs? Then you need a bridging table to handle the many-to-many relationship there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
And one last suggestion, you should NOT store calculated data like CumulativeGPA. You should instead calculate that as you need it. This is like storing Age, as soon as you store it the value is stale.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
While I agree with everyone that these tables are not properly normalized, in an effort to help, can you tell me which tables you're trying to understand the relationships between?
You need to first identify your primary key value on each table. How would any one row on that table be uniquely special and independent of all other rows. It can be one column or several, but you have to identify that. Once you've identified that, then, you need to look to the relationship to another table. Which one is the parent and which the child? The primary key of the parent will need to be included in the column list of the child and that will allow you to set up the foreign key relationship. It all starts with the primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thankyou thats the logic I needed SSCoach
-
Sure thing. Best of luck. Report back what you get for a grade. I'm curious.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply