a problem with the rules of one to many relationships(urgent!!)

[ngoz1]

If you have a one to many relationship between two entities e.g employee and department. with employee being on the one side and department being on the many side.

Lets say the many side is non obligatory(ie optional), will this type of relationship require 3 tables ie one for the employee, one for the department and a third table containing the primary keys of both the employee and department ie a join table(just like is done with many to many relationships)?
if so, why? and if so, which of the primary keys in the third table( which are now foreign keys) will be the primary key

 

[petersdaniel]

You situation sounds a bit confusing. First of all, in most business models, an employee is only assigned to one department not many departments. An employee might be assigned to many projects or tasks or etc..

So, taking a standard business model for managing a corporate structure, I might look at my cor entities as follow

Company -> Divisions -> Departments -> Employees

Without getting too far into the weeds, I would create Departments table as a table with Primary Key of DepartmentID and a field of DepartmentName. The DepartmentID would be defined as unique as well as the Department Name,

The Employee table would have a primary key of EmployeeID and all the other employee info I would set EmployeeID up as a Unique Key and I would also enforce a unique rule over something like the SSN number to ensure an employee could only exist once in the table, Additionally, there would be a field of DepartmentID in this table which would be fed from the Departments table. An employee could could only belong to one department under this configuration. The relationship of Departments to Employees would be a ONE to MANY petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[ngoz1]

please dont get too hung up on my example it was just an example i created off the top of my head to expalin my question.
all i am really trying to find out is "what is right thing to do in a one to many relationships were the many side is optional."

let me give you a real life example from the database i am creating for BWA :-
entities: member and payment
a member may make one or many payments(optional)(usually a registration payments which is renewed every year)

but a particular payment is made by one member.

1)am i right in the way i am relating the two entities and

2)if so,seeing that the many side is optional, do i just post the member primary key into the payment table or do i create a third table between member and payment and post the member id and payment id into this table (just like i would do if this was a many to many relationship)

3)if a third table is created, which of the keys is meant to be the primaty key - member id or payment id?


cheers and thanks for your time
ng