Table structure

[Kayleigh]

I am considering the best way to structure one component of my DB.
The scenario is that each person has certificates so there is a table for people and another table for certificates. However we also have the education details for some people which we record in another table with foreign key to people table. My question is how can I have certificates linked to the education table whilst allowing certificates to be entered individually not linked to a specific record in education?

 

[plog]

Indirectly--
Education is directly connected to people.
Certificates is directly connected to people.
Education isn't directly connected to people but you can go thru people to get there.

If that doesn't work, provide a more concrete example to demonstrate.

 

[Kayleigh]

So you are saying to have two parallel tables and the connection between education and certificates is indirect but I would like to associate a certificate with an education record. Would it be denormalising the data to have 2 foreign keys in certificates - to education, as well as to staff?

 

[CJ_London]

The scenario is that each person has certificates so there is a table for people and another table for certificates

this does not sound right to me - the relationship should be many to many (people, certificates and a joining table). Or are you saying only one person can have 'certificate A'? It would be this joining table that would hold a link to the education table if such a record exists.

I'm guessing the education table does not have any structure as such - for example if 5 people have education 'class A' there will be 5 repeats of 'class A'

 

[Pat Hartman]

Aren't certificates always connected to some type of education, even if it is just, read this pamphlet and take a test? So you have both formal and informal education.

People-->Education--->Certificates

 

[The_Doc_Man]

So you are saying to have two parallel tables and the connection between education and certificates is indirect but I would like to associate a certificate with an education record. Would it be denormalising the data to have 2 foreign keys in certificates - to education, as well as to staff?

As other have mentioned, it depends. If you need to take a course to get a certificate and that is the ONLY way you get a certificate, then the certificate can be as simple as a check-box on the educational course.

However, I have a feeling that it is possible to take a course and get credits but not an actual certificate, and it is also possible to "challenge" a certificate without the course. If that is the case, then your insistence on their linkage is misplaced. The linkage in the latter case is illusory. If it is possible to get one without the other, there is no linkage and it is, in fact, a parallel relationship.

 

[plog]

certificates to be entered individually not linked to a specific record in education?
... I would like to associate a certificate with an education record.

So, certificates may or may not be associated with education. In that case I would make all certificates relate to an education record and put a dummy record in education that unassociated certificates link to.