Table Design re one-to-one relationships

[lmcc007]

I am having trouble with this db because I want to throw everything into one table because of the one-to-one relationship with Event table. I’ve been taught that every subject should have its own table.

The purpose of this db is to keep track of all the media, applications, release forms, and so on distributed.

My difficulties are with Media and Meeting tables. Sometimes media will be sent out prior to meeting and sometimes the media is given at the meeting or an extra copy (courtesy copy) is provided to the company. I don’t want to count it twice if an original was given and then a courtesy copy was given. If a rep cold-call ABC and send them media prior to meeting, and then when meeting give them a courtesy copy I don’t want media to be counted as two. It should be like one original and one copy. I don’t want it to appear as though the reps have been hounding this company and sending media to the same company over and over again for the same contract.


Here’s how it suppose to work:

1. Choose a company name from Company table

2. Click a button on the form to create an Event

3. Enter the Date and time. If the Event Type is 59, then meeting form will open, or

4. If the Event Type is 58, then media form will open so I can say what I sent and so on.

Attached is the db so far.

Any suggestions on the setup and relationships?

 

[jzwp22]

Is media only provided in association with an event or can it be independent from an event?

From the sound of it, I see a one-to-many relationship not a one-to-one. I would assume that you can have many events associated with a customer. What do you consider an event?


(PS: I could not look at your database since I don't have Access 2007 here at work)

 

[lmcc007]

Is media only provided in association with an event or can it be independent from an event?

From the sound of it, I see a one-to-many relationship not a one-to-one. I would assume that you can have many events associated with a customer. What do you consider an event?

Yes, one customers can have many events.

An event is: cold calling, meetings, preparing for presentations, researching, receiving mailing, sending e-mails, and so on. Basically, any activity going on. After thinking about it some more, I decided I needed one big Event table. (I get cared away with this subject per table thing. I see why some colleges have a whole semester just on dealing with subjects/tables.)

It was suggested to me that I should have a table for media. So, if I send a brochure out prior to meeting, I can count that brochure. Then while meeting with clients and I give them a courtesy copy, I can say in the field how many copies were left and so on.

Now, as I am typing I am begining to see what he's talking about--a media table. Hmmm... would take care of that. That way I can be more specific on what was left and how many. Meaning, brochure, photos, release forms, etc.

See it helps to talk it out with others some time.

 

[jzwp22]

Yes, but would sending media out be considered an event as well? I assume that the events themselves are used for all clients: cold calling, meetings, preparing for presentations, researching, receiving mailing, sending e-mails etc.

Based on what you have described, this is how I would structure the tables

tblCustomers
-pkCustID primary key, autonumber
-txtCustomerName
other fields related to the customer

tblEventTypes (1 record for each type of event: cold calling, meetings, preparing for presentations, researching, receiving mailing, sending e-mails etc.)
-pkEventTypeID primary key, autonumber
-txtEventType


tblCustomerEvents
-pkCustEventID primary key, autonumber
-fkCustID foreign key to tblCustomers
-fkEventTypeID foreign key to tblEventTypes
-dteEvent (date of event)
other fields related to the event

A table to hold the various media types that can be sent to a customer
tblMediaTypes (1 record for each type of media: brochure, photos, release forms etc.)
-pkMediaTypeID primary key, autonumber
-txtMediaType

tblCustomerEventMedia
-pkCustEventMediaID primary key, autonumber
-fkCustEventID foreign key to tblCustomerEvents
-fkMediaTypeID foreign key to tblMediaType
-qtyProvided (quantity of media of the particular type provided to the customer during this event)

 

[lmcc007]

Okay, I never thought about putting Event Type in a separate table. I am just thinking that Event Type goes inside of Event table--that is:

tblEvent
EventID = pk
EventDate
EventTime
EventType = Number (get the type from a lookup table)
EventDescription


Let me actually do what you suggested and see how it works then I get back.

 

[jzwp22]

Okay, I never thought about putting Event Type in a separate table. I am just thinking that Event Type goes inside of Event table--that is:

tblEvent
EventID = pk
EventDate
EventTime
EventType = Number (get the type from a lookup table)
EventDescription

Correct, but I would not recommend using a lookup at the table level. Check out this site for the problems that it can cause. You would set up the EventType field as a long number datatype and then use a combo box on a form to populate it.

 

[lmcc007]

Attached is what I have so far.

I'm not following you on tblCustomerEventMedia. How do I jump to there to enter the quantity?

 

[jzwp22]

Sorry, I don't have Access 2007 here at work, so I cannot open your database.

You would have a form based on the customer table then a subform (in that form) based on the tblCustomerEvents (the event type would be populated via a combo box). Then within that subform you will have another subform (form-subform-subsubform setup) to enter the quantity and media type. The media type would be populated using a combo box.

 

[lmcc007]

Okay, here it is in Access 2000.

 

[jzwp22]

You'll need to explain the event followup and event followup date fields in the event table. Do these fields represent a future event that must occur to satisfy the event that is entered?

 

[lmcc007]

You'll need to explain the event followup and event followup date fields in the event table. Do these fields represent a future event that must occur to satisfy the event that is entered?

Yes and no. The future event is not a must, just a reminder. And, those two fields may be deleted. Tracking the media is the most important.

 

[jzwp22]

I have updated the relationships and added a form (using the form-subform-subsubform setup) to your database. The modified version is attached.

I asked about the future event because if you want to track an event related to another event then that would require a different table structure then the one I proposed earlier.

 

[lmcc007]

I have updated the relationships and added a form (using the form-subform-subsubform setup) to your database. The modified version is attached.

I asked about the future event because if you want to track an event related to another event then that would require a different table structure then the one I proposed earlier.

Gotcha. Regarding "...future event...", I want to keep this db simple as possible. My head is hurting from the others

Thanks!

 

[gemma-the-husky]

ie, if you have a true one to one link, then there is generally no need for the data to be separated, although there are certain special circumstances where it may be useful.

but in most cases, it represents a mis-analysis of the data (or it really s a one-to-many when you stop and think about it)

a one to one link implies that for each value in table A there can only be at most 1 value in table B, and the same in reverse. A normal lookup is one-to many.

 

[jzwp22]

You're welcome. Good luck with the project.

 

[lmcc007]

Okay, maybe I am getting the Event table wrong, which consists of the following:

tblEvent
EventID
EventDate
EventTime
EventType (i.e., send media, receive mail, phone calls, meeting, any type of activity)
EventDescription
EventNote
​

Two of the event types will require further questions. For example:

EventType = Send media -- questions that follow are:
1. Media type – brochure, pamphlet, CD, DVD, release form…
2. Fee Agreement?
3. Authorization form?
4. Ford ad copy?
​

EventType = Meeting – question that follow are:
1. Meeting regarding pipeline workers
2. General presentation and references?
3. Fee agreement signed?
4. Authorization form?
5. Courtesy copy?
6. Form ad copy?
7. Notes about meeting
​

It is one-to-one with tblEvent because each time you send something it will be a new record—that way the date and time is tracked also.

 

[jzwp22]

If you look in the database I posted, the event table is the many side of a relationship with respect to the customer: one(customer)-to-many(events).

As I structured it, you can tie multiple types of media to an event. I'm a little lost on the questions aspect, I thought you just wanted to record the types of media and quantities of each that were sent relative to an event?

Now looking what you posted relative to a meeting event:

EventType = Meeting – question that follow are:
1. Meeting regarding pipeline workers
2. General presentation and references?
3. Fee agreement signed?
4. Authorization form?
5. Courtesy copy?
6. Form ad copy?
7. Notes about meeting


It looks like items 3 through 6 are types of media, so they would be recorded in the tblCompanyEventMedia. If you want to have a field to record the notes of a meeting, you would need to add a field to tblEvents (this will allow you to record notes for any event not just the one particular type of event, i.e. meeting)

I do not know what items 1 and 2 represent. Is 1 just a description of the event? If so, you want to add a description field to tblEvent. What are the general presentation & references described in item 1? Are these documents and hence media?

 

[The_Doc_Man]

Just a fine point regarding one-to-one tables... I have only seen a very few cases where 1/1 relationships made sense. Consider this a "file for future reference" thing.

A. You have two or more fairly narrow tables bearing 1/1 relationship. This can ONLY make sense when there is a security difference between the fields in table X and the fields in table Y, etc., or in the bizarre case where the tables, though related within your database, reside on and are linked from multiple back-end servers.

B. You have not less than three tables each bearing 1/1 relationship. One of these tables contains common data and might be considered a "parent" even though 1/1 is not normally a parent/child situation. The other tables contain supplemental data that is not always processed - and which is truly independent of the data in the common table but yet dependent on exactly the same primary key.

Because of storage issues with MEMO fields and BLOB fields, this would again make sense.

C. Despite having the same security requirements, the information might be required by some government regulation to be stored separately for data design purposes. Even though you really DON'T isolate the data on forms and such. Since I work with the Department of Defense, I run into that one once in a blue moon.

D. VERY RARE CASE: Because of long data fields, you have case B but you also have enough data that you could overflow the 2048 bytes per record limit even not counting MEMO and BLOB cases, such that the only way to process the information is to use the tables either separately or use the parent table and only one of the other tables at a given time, then go back and process the other combinations with the parent. This could also occur if you have more than 255 fields to track for a single primary key.

Case D almost always suggests that you have non-normalized data, but I'm leaving the door open for it to be just very lengthy text or currency fields or really strange data sets with lots of dependent fields that chew up record bytes or the field-number limit at a furious clip.

Any other time, 1/1 relationships are a waste of table overhead.

 

[lmcc007]

jzwp22,

I got what you did.

I was trying to explain my thinking regarding my one-to-one confusion:

tblEventTypes is a lookup table. You go:

EventDate
EventTime
EventType (from tblEventTypes)

If EventType is media then form opens re media sent
If EventType is meeting then form opens re meeting and media left

I was wanting to do a table for Media and a table for Meeting, because I saw them as two different subjects. You know you talk only about the Media you sent out and stuff; then in Meeting you talk only about the meeting you had. But, both media and meeting are events.

 

[lmcc007]

B. ...One of these tables contains common data and might be considered a "parent" even though 1/1 is not normally a parent/child situation. The other tables contain supplemental data that is not always processed - and which is truly independent of the data in the common table but yet dependent on exactly the same primary key.

Do you have an example of "The other tables containing supplemental data that is not always processed"?

I think this is where my confusion lies.

Many of the events that will be created will not have to answer information regarding Media or Meeting. That’s why I kept thinking about separate tables for Media and Meeting and joining them one-to-one with the Event table. Event is important because it tracks the Date and Time of each transaction.

I have a learning disability; it takes me a while to get some things; I have to keep going back over what I read.

Anyway, good explanation, The_Doc_Man. Thanks!