Primary Key Foreign Key 2 tables?

Question

Hi guys again same question
i have table called employee and table called education. i wanted to split the database employee because is just too many columns
question is:
can the employee_no be used by education table as a primary key? like

constraint pk1(emp_no) primary key
constraint fk1(emp_no) foreign key references employee(emp_no)

what do you guys suggest? or maybe use a composite key (emp_no,emp_qualification)
14 minutes ago

check this link

http : // img442. imageshack. us/img442/5594/empedu.jpg

Answer 1

I have to agree with Ratchetr. If it is just a single item, you don't need to split it out of the employe table. What would make more sense is if you had an Employee and an Education table that might be a many to many relationship. For example Employee1 and Employee2 both could have a BA in Computer Science, and Employee1 could also have an MBA.



Of course if your Business Rules don't call for that, I would just leave Education in the employee table unless the assignment specifically tells you to break it out, and then yes, you could use the EmployeeID

Answer 2

>> (YES i want only one education per employee)

If that's true, then splitting it into multiple tables doesn't smell right. You're adding complexity where it may not be needed. (grinning's point is valid, but that's rather extreme normalization that adds even more complexity. Sounds like it isn't justified here).



What is your definition of too many columns? MySql allows 4096. Oracle 1000. Microsoft is 1024, or 30000 if you really need it.



Are you really hitting those? If so, I suspect there is something else wrong here. 1,000 columns for an employee would seem more than adequate.

Answer 3

Create education as a dimension of employee, as follows:



edu_id (long integer)

qualification_desc (text)

qualification_year (integer)

specialization (text)



Then you just have a single row for each *combination* of the three attributes (description, year and specialization) and the ID is applied as a foreign key in employee. So two employees who specialize in the same area and who took the same qualification in the same year can be applied the same edu_id in employee. You could even create a dummy row in education that indicates "No qualification" to apply to all the employees that haven't completed anything.



Good luck.

Answer 4

You should use a composite key unless you only want one education per employee.



P.S. You don't have to split up the link.

Answer 5

There can be only one 'primary key' in a table. Try, CREATE TABLE VIOLATIONCITATION (ViolationCitationID Int PRIMARY KEY, VIOLATIONID CHAR(5) REFERENCES VIOLATION(VIOLATIONID), CITATIONID CHAR(5) REFERENCES CITATION(CITATIONID), FINECHARGED DECIMAL(5,2) );