ACCESS FEED: Re: [AccessDevelopers] New DB Design Ideas

Jumat, 01 Juni 2012

Re: [AccessDevelopers] New DB Design Ideas

Lets start by defining terminology just so we can make sure we are on the same page. A table is very similar to a spreadsheet. In a spreadsheet we have Rows and Columns. In a database table, the columns are called Fields and the rows are called Records. You define the table by defining what fields it will store. Then you add records (or rows) to the table.

It is perfectly usual to have some fields (or columns) in the table blank for some records. For example, let say a table was defined this way:

tbl_MemberInfo

--------------------

member_ID (AutoNumber)

member_Name (Text)

member_Age (Number)

member_Join_Date (Date)

member_State (Text)

Then it might have data like this:

ID Name Age JoinDate State

1 Toby 37 7/12/2011 OR

2 John 9/22/2011

It is fine that there are a few blank fields in the 2nd record. You define all the fields that you will need for at least one record, and not every record will have data in every single field.

With Duane's question about normalization and what you are storing, say in my hypothetical example above that we wanted to records Events that Members attended. Instead of adding a field to the Member table called Events, and then trying to store a list of Events in that field, you would typically create a separate table called tbl_Events such as this this:

tbl_Events

------------

Event_ID

Event_Name

Event_Date

ID Name Date

1 Event A 5/20/2012

2 Event B 5/22/2012

Since members can attend more than one event, and each event can be attended by more than one member, we have a Many-to-Many relationship between Events and Members, and we require a 3rd table to indicate which members attended which events.

tbl_Event_Member

-----------------------

Event_ID

Member_ID

Event_ID Member_ID

1 2

1 1

2 2

3 1

3 2

4 1

I'm still not sure at all what you are trying to store, so sorry I can't be more specific to your situation.

Hope this helps,

Toby

From: John Moss [mailto:mossj5@comcast.net]
To: AccessDevelopers@yahoogroups.com
Sent: Fri, 01 Jun 2012 04:52:40 -0700
Subject: Re: [AccessDevelopers] New DB Design Ideas

That's the problem I'm struggling with.

Some files may have those fields, some may not. But if they don't, that info is needed as well.

On 5/31/2012 9:29 PM, Duane Hookom wrote:

What are these fields for:
Maintenance
Recalls
Keepers
Secondary

I wonder if this structure is un-normalized.

Duane Hookom
MS Access MVP

To: AccessDevelopers@yahoogroups.com
From: mossj5@comcast.net
Date: Thu, 31 May 2012 17:54:10 -0600
Subject: Re: [AccessDevelopers] New DB Design Ideas

Toby,
Here's the data each file may or may not have:

Transaction Count
Session #
Amount
Maintenance
Recalls
Keepers
Secondary

If I use a single table with a field for each entry, I'll end up with empty fields. For example, if file one has these fields:

Transaction Count
Session #
Amount
Maintenance
Recalls
Keepers

and file 2 has these fields

Maintenance
Recalls
Keepers

and file 3 has this field

Secondary

then the first set of fields will be empty for file 3 and there will be empty fields for file 2..

But as I type and think this through that may not be a bad idea. That way you'll know if the file had only transaction or transactions and maintenance. A Null valuewould mean either no data was entered or it was missed.

Does that make sense?

Thanks,
John

On 5/31/2012 4:05 PM, Toby Bierly wrote:

You should be able to setup a table with the following fields:

Date, Client, File_Name, Count, Transaction_Number, Amount, etc.

You would enter multiple files on any given date, and record the client it was for, and the other info you specified.

I'm not sure how you are ending up with empty rows.

Hope this helps,

Toby

-----Original Message-----
From: AccessDevelopers@yahoogroups.com [mailto:AccessDevelopers@yahoogroups.com] On Behalf Of yshopper
Sent: Thursday, May 31, 2012 1:29 PM
To: AccessDevelopers@yahoogroups.com
Subject: [AccessDevelopers] New DB Design Ideas

I am starting a new project that will track Daily Tasks. One of the tasks is to download and process files. Each day the number of files will change. I need to record the name of the file and several other pieces of info after the file is processed; i.e. Count, Transaction Number, Amount, etc.

Some days there will be only 10-12 files, other days there could be upwards of 70-75 files. I need to record each file for each day. Also, these files are associated with 3 different clients.

How would I design a table(s) that will allow this type of data? I have a DB setup but it ends up with empty rows on all but the busiest days. I would like to avoid that if possible.

Thanks for any tips or suggestions,
John