Database Design

[alvinisadore]

I have been committed to doing a database based on an application; yet I can't seem to get a proper relationship in. My tables are Applicant - t, Criminal -t, Application - T. Is it possible to do a one to one relationship between these tables? I figure an applicant has or doesn't have a criminal background; an Application is filled out by one person only. I guess the primary keys should be the same from the Applicant - t. I'm not good with one to one relationships. HELP!!! How should I connect these tables?

[JConsulting]

I have been committed to doing a database based on an application; yet I can't seem to get a proper relationship in. My tables are Applicant - t, Criminal -t, Application - T. Is it possible to do a one to one relationship between these tables? I figure an applicant has or doesn't have a criminal background; an Application is filled out by one person only. I guess the primary keys should be the same from the Applicant - t. I'm not good with one to one relationships. HELP!!! How should I connect these tables?

If its one to one, then put it into the same table.

[orangeCat]

I have been committed to doing a database based on an application; yet I can't seem to get a proper relationship in. My tables are Applicant - t, Criminal -t, Application - T. Is it possible to do a one to one relationship between these tables? I figure an applicant has or doesn't have a criminal background; an Application is filled out by one person only. I guess the primary keys should be the same from the Applicant - t. I'm not good with one to one relationships. HELP!!! How should I connect these tables?

This info may be useful
Relational Data Base

[mshmyob]

As suggested, depending on what you are doing then you can have either a 1-1 or 1 to Many between Applicant and Criminal.

If you do just need to designate if an Applicant has a criminal background then a simple YES/NO field in the Applicant table will suffice.

If you need to say track types of criminal records an applicant has you will have a 1 to Many or a Many to Many depending on other factors.

If you can give us more details (business rules) then we can give you more guidance.

cheers,

I have been committed to doing a database based on an application; yet I can't seem to get a proper relationship in. My tables are Applicant - t, Criminal -t, Application - T. Is it possible to do a one to one relationship between these tables? I figure an applicant has or doesn't have a criminal background; an Application is filled out by one person only. I guess the primary keys should be the same from the Applicant - t. I'm not good with one to one relationships. HELP!!! How should I connect these tables?

[alvinisadore]

The applicant submits only one application. The applicant may or may not have Criminal, Mental, Medical, Alcohol/Drug background. I am unable to make tables or at least whn I get one, the primary keys dont relate.

I had applicantid as the primary key in each one to one table with join #2 but the information does not cross over. When I put every thing in one table I still have problems.

[Stewart Ross]

The applicant submits only one application. The applicant may or may not have Criminal, Mental, Medical, Alcohol/Drug background. I am unable to make tables or at least whn I get one, the primary keys dont relate.

I had applicantid as the primary key in each one to one table with join #2 but the information does not cross over. When I put every thing in one table I still have problems

Hi. One feature of the relations you describe is that many of them are optional - you may have a one to zero or one relationship (an applicant may not have any criminal record, for instance). Joining the tables in an Access query using a standard inner join (where both fields are equal) will exclude all rows where these optional relations are not filled in - and in the worst case, where all applicants have one or more unfilled options, you will see no rows at all.

Another name for this kind of join between tables is an equi-join - rows are shown where the joined fields are equal. Where there is a null on one side of an equi join there is no equality, so the row is not returned. The relationships are valid - but with equi-joins the whole row is excluded if there is no entry in any one of the optional joins.

What you need to do in these circumstances is to use a left-join in your queries between the Applicants table and all those which represent optional (one to zero or more) relations. This way you will always see the applicant data, and only see the related table data for criminal record etc if it exists.

It is because of such complications that the normal practice in designing database tables is to merge back 1-1 table relations into a single table. There can be very good reasons not to do so, of course, in which case left-joining the optional tables is essential.

Left-joining can be done when you set the relationships (in the relationship window) for the table as a whole, or in the query editor for individual queries. Click on the relationship line to select it, right-click and select edit relationship, Join Type, then change the relationship to 'include all rows from Applicants, and only rows from ... where the joined fields are equal'.

-Stewart

[NeoPa]

Please remember to provide a meaningful Title for any threads started (Please Use Appropriate Titles for New Threads!). This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

ADMIN.

[mshmyob]

Sounds like you don't have a foreign key. You do not join PK's to PK's. Show us your schema.

Do you need to track the type of 'Criminal' record applicants have. In other words do you need to specify the type such as Criminal, Mental, Medical etc.,. Do you need to do counts or reports based on those types.

If so then you would have one table for Applicant with ApplicantID as the PK and one table for Record with RecordID as the PK and in the Applicant table you would have a field called RecordID that links the 2 tables.

This will give you a 1 to Many relationship. Each Applicant can have 1 type of record but Each type of record can have Many applicants.

If you need to indicate multiple types of records then you put a bridge table between.

Does any of this sound like what you want to do. If so we can elaborate a little more for you.

cheers,

The applicant submits only one application. The applicant may or may not have Criminal, Mental, Medical, Alcohol/Drug background. I am unable to make tables or at least whn I get one, the primary keys dont relate.

I had applicantid as the primary key in each one to one table with join #2 but the information does not cross over. When I put every thing in one table I still have problems.