Yet another relationships question

[XQuestion]

I can't seem to wrap my mind around the concept.

I'm working on a database to track technical documents that is supposed to be as flexible as possible.

The tables I've created are:

tblDocumentTitle
DocumentTitle
DocumentTitleID

tblDocumentNumber
DocumentNumber
DocumentNumberID

tlbDocumentDescription
DocumentDescription
DocumentDescriptionID

tblVersionNumber
VersionNumber
VersionNumberID

tblVersionDate
VersionDate
VersionDateID

tblDocumentStatus
DocumentStatus (Relased, In Review, In Work, Archived)
DocumentStatusID

tblStatusName (we need the name of the person reviewing or working on the doc if it's In Review or In Work)
StatusName
StatusNameID

tblDocumentAuthor
AuthorFirstName
AuthorLastName
AuthorCompany
DocumentAuthorID

tblDocumentOwner
OwnerFirstName
OwnerLastName
OwnerCompany
DocumentOwnerID

tblClientName
ClientName
ClientNameID

All of the ID fields are autonumbered primary keys. I haven't added any foreign keys because I'm not sure which I'll need. And, quite frankly, I'm not getting the concept of relationships so I'm not sure how to relate any of these tables. I'd appreciate any light anyone can shine on the subject for me.

 

[jeremie_ingram]

Relationships

What you have to keep in mind is there are different types of relationships between tables; One-to-Many, One-to-One, and Many-to-Many. The prefered relationship between tables is One-to-Many, because it usually indocates that you have normalized the table properly.

From the description of your tables, I would say you went way overboard. Many of these tables will have a One-to-one relationship, which indicates they should have been in the same table to begin with. If you are gathering data regarding an object, you would note only the solid attributes/features of it and keep those in one table. All other related topics should be in another. If there is a one to one relationship (Book - Title) they should be in the same table, but if there is more than one option to the relationship there should be a second table to contain that data (Book - Book Stores selling it).

The following is IMHO the way it should go. The bold items having relationships to their perspective tables. In this exmple, there is a One-to-Many relationship between the tblDocument(one) to tblAurthor(many) since the Document table contains the foriegn key of the Aurthor table. A foriegn key is usually the primary key from the other table. But also not that there can be many version numbers per document because the version number table has the Document foriegn key.
Let me know if htis helps clarify anything. I am willing to help.


tblDocument
DocumentPKey~
DocumentTitle
DocumentNumber
DocumentDescription
Author
Owner

tblVersionNumber
VersionNumberPKey~
VersionNumber
VersionDate
Document

tblDocumentStatus
DocumentStatusPKey~
DocumentStatus (lookup w/ entered values)
Document

tblAuthor
AuthorPKey~
AuthorFName
AuthorLName
AuthorCompany


tblOwner
OPKey~
OFName
OLName
OCompany

 

[XQuestion]

This is making more sense now--thank you! Follow up question for you. If I wanted to track the name of the person who is working on or reviewing the document if DocumentStatus = "In Work" or "In Review," would the structure be as follows?

tblStatusName
StatusNamePKey~
StatusName (I don't really need to separate out first and last names here)
DocumentStatus

 

[jeremie_ingram]

Its late, I am tired, but here is a small example. Tell me if this is the path you are trying to go down.

Look in the relationship window and see what is happening. It is a very simple exapmle, but we can work from this common ground.

 

[XQuestion]

Yes, that's it.

 

[The_Doc_Man]

XQ - here's the rule of thumb for adding data to a table (beware of falling hammers...).

Look at the prime key of the tables you have. Look at the derivation of the field you want to add. Your decision will be "new field in existing table" or "new field in new table." The latter sub-divides into "totally new table" or "linking table."

1. If among all of the tables you have, you find a single table for which your new field depends entirely and only on the prime key of a particular table, the field can be added to the existing table.

2. If you cannot find a single table that meets the derivation criteria, see if a combination of tables meets the derivation criteria. If a combination completely meets the requirements, you have a linking table case, and the "new" field goes in the linking table. The linking table will also store (as foreign keys) the corresponding prime key values from the selected tables.

3. If no combination of tables meets the criteria, you have a new table that requires its own set of keys. (Some of which can be foreign, too.)

Some folks will ask what is different between two and three. The question is whether a new prime key is required.

Let's see an example.

An insurance agent can sell policies to many customers. The agent can draw on many underwriters. A customer can buy more than one policy.

Here, you need a table for agents, a table for customers, a table for underwriters, and a table for policies. The agent table has a prime key of the agent's identifying number, probably either an employee number or a business license number. The underwriters table has a prime key of the underwriter's identifying number, possibly autonumber. The customer table has a customer number, probably autonumber.

What about the policy table? Well, you need more than one key from existing tables, so here is a linking table. Your foreign keys will include the agent, the customer, and the underwriter. The extra key is the policy number. Case 3.

Now let's add some fields. Add the customer's phone number to the customer table. Add the date of policy purchase to the policy table. Add the cost of the policy to the policy table. Add the address of the underwriter to the underwriter table. No new keys are required for these, so all case 1. Once you have the right tables set up, most questions like these are no-brainers.

A trickier one: Add a restriction that some agents cannot write policies using certain underwriters. (Don't ask me what they did to deserve the restriction...). You could take this as a way to say - what underwriters CAN they use? This would be a new table as a link but with no new fields. You would make entries in this table showing the agent PK and underwriter PK but nothing else. Then, once this is set up, your agent can write policies only for those agencies for which an entry exists in this linking table. The existence of the record itself is the embodiment of the restriction (actually, turned around to make it a permission) and no NEW keys are required. This is an example of case 2.