A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

One table or Two?



 
 
Thread Tools Display Modes
  #1  
Old July 3rd, 2009, 12:02 AM posted to microsoft.public.access.tablesdbdesign
DUNNER7
external usenet poster
 
Posts: 39
Default One table or Two?

I am progressing with my student database, thanks for all help so far, my
next problem is this. I want to record student discipline infractions. This
means that I will have a table to record each individual incident as reported
by a staff member and the table will contain basic info, ie. time, location,
rule violated, brief description of event. I will probably design a form
based on the table for the staff member to enter each incident. I want this
information stored into the table and sent to whomever will deal with the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each infraction
will have to enter relevant information ie. date, punishement type, length
of punishment, parent contact made, brief description. This information has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two. Each
incident and Each disposition would have it own unique number (auto number)?

Thanks,
Del Dobbs
  #2  
Old July 3rd, 2009, 02:33 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default One table or Two?

well, you need to analyze your real-world process. will a single instance of
an infraction ever result in more than one consequence being assigned? for
instance, what if detention is assigned, and perhaps started, but not
completed for some reason? and will multiple infractions ever be assigned a
single punishment? you'll know the possible variations best, of course.

once you've defined the situations that the table structure needs to
support, then define the relationship between infractions and consequences:
is it one-to-one, one-to-many, or many-to-many? when you've defined the
relationship, you'll know what table(s) you need to support it.

just a tip: you'll probably want to find a middle ground between what
happens most of the time, and what could *conceiveably* happen. it's not
realistic to build a structure that will support EVERY possible scenario;
but you need to draw the line at the point where you can see a reasonable,
workable way to "squeeze" or "tweak" highly unusual real-world events a bit,
so they fit into the standard parameters you've defined.

hth


"DUNNER7" wrote in message
...

I am progressing with my student database, thanks for all help so far, my
next problem is this. I want to record student discipline infractions.

This
means that I will have a table to record each individual incident as

reported
by a staff member and the table will contain basic info, ie. time,

location,
rule violated, brief description of event. I will probably design a form
based on the table for the staff member to enter each incident. I want

this
information stored into the table and sent to whomever will deal with the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each

infraction
will have to enter relevant information ie. date, punishement type,

length
of punishment, parent contact made, brief description. This information

has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two.

Each
incident and Each disposition would have it own unique number (auto

number)?

Thanks,
Del Dobbs



  #3  
Old July 3rd, 2009, 03:33 AM posted to microsoft.public.access.tablesdbdesign
DUNNER7
external usenet poster
 
Posts: 39
Default One table or Two?

You computer people speak in "computerese" (lol). I hope to be one of you
someday and be able to answer questions. After reading your reply several
times, I think I see what you are saying.

Student could have a minor infractions (discipline incident) be assinged
detention (consequence incident) 1:1 Student could blow-off detention and
need stronger consequence. Does that get added to the original infraction or
does that incident of "blowing-off" detention get assigned a new
consequence...meaning the first incident was never technically resolved?
Right?

Or student could come down to your office after 3 seperate incidents and you
just get to them before the day ends and assign one consequence to all 3
incidents.
Many:Many?

So I guess you are recommending the two table approach one for discipline
incidents and one for the dispositions?

Then if I was to run a report for a student's discipline history I would
need to do a query to take the relevant infraction information and pair it
with the disposition information to form the report. Correct?

I was designing my discipline table and it goes like this:

DisciplineID (pk) auto
StudentID (lookup student ID in Demographics table)
Violation (lookup violation table)
TimeOccured (date/time)
Location (lookup in location table)
ReportedBy (lookup in staff table)
Narrative (memo)

Does that seem right should I use all of the lookups from other tables?

Thanks,
Del Dobbs

"tina" wrote:

well, you need to analyze your real-world process. will a single instance of
an infraction ever result in more than one consequence being assigned? for
instance, what if detention is assigned, and perhaps started, but not
completed for some reason? and will multiple infractions ever be assigned a
single punishment? you'll know the possible variations best, of course.

once you've defined the situations that the table structure needs to
support, then define the relationship between infractions and consequences:
is it one-to-one, one-to-many, or many-to-many? when you've defined the
relationship, you'll know what table(s) you need to support it.

just a tip: you'll probably want to find a middle ground between what
happens most of the time, and what could *conceiveably* happen. it's not
realistic to build a structure that will support EVERY possible scenario;
but you need to draw the line at the point where you can see a reasonable,
workable way to "squeeze" or "tweak" highly unusual real-world events a bit,
so they fit into the standard parameters you've defined.

hth


"DUNNER7" wrote in message
...

I am progressing with my student database, thanks for all help so far, my
next problem is this. I want to record student discipline infractions.

This
means that I will have a table to record each individual incident as

reported
by a staff member and the table will contain basic info, ie. time,

location,
rule violated, brief description of event. I will probably design a form
based on the table for the staff member to enter each incident. I want

this
information stored into the table and sent to whomever will deal with the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each

infraction
will have to enter relevant information ie. date, punishement type,

length
of punishment, parent contact made, brief description. This information

has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two.

Each
incident and Each disposition would have it own unique number (auto

number)?

Thanks,
Del Dobbs




  #4  
Old July 3rd, 2009, 04:44 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default One table or Two?

On Thu, 2 Jul 2009 16:02:01 -0700, DUNNER7
wrote:

I am progressing with my student database, thanks for all help so far, my
next problem is this. I want to record student discipline infractions. This
means that I will have a table to record each individual incident as reported
by a staff member and the table will contain basic info, ie. time, location,
rule violated, brief description of event. I will probably design a form
based on the table for the staff member to enter each incident. I want this
information stored into the table and sent to whomever will deal with the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each infraction
will have to enter relevant information ie. date, punishement type, length
of punishment, parent contact made, brief description. This information has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two. Each
incident and Each disposition would have it own unique number (auto number)?

Thanks,
Del Dobbs


Part of the question is: will each violation involve one and only one
disposition? Or might a student have (say) detention, an essay to write
explaining why he'll never do it again, and flogging? (oops, showing my
age...)

If only one disposition will ever be needed, one table will do; if you want to
allow for the possiblity of multiple outcomes, followups, etc. then a second
table in a one-to-many relationship would be more appropriate.

--

John W. Vinson [MVP]
  #5  
Old July 3rd, 2009, 04:13 PM posted to microsoft.public.access.tablesdbdesign
DUNNER7
external usenet poster
 
Posts: 39
Default One table or Two?

Thanks, you take me back to my original question. If I am able to do this
with one table (infractions/dispositions) together.... can two seperate
people, 1: the reporer of the incident and, 2: the person who handles the
disposition enter data into the same table using two different forms. For
example I would design form that would enter specifics of the
infaction...form would be sent to admin (disposition part), person could view
infraction, hit command button, pull up form to enter disposition, hit enter,
and disposition information is entered onto same row as infraction
information in the fields set aside for disposition data in one table??

It would be nice to be able to flogg a child every once in awhile.


Del

"John W. Vinson" wrote:

On Thu, 2 Jul 2009 16:02:01 -0700, DUNNER7
wrote:

I am progressing with my student database, thanks for all help so far, my
next problem is this. I want to record student discipline infractions. This
means that I will have a table to record each individual incident as reported
by a staff member and the table will contain basic info, ie. time, location,
rule violated, brief description of event. I will probably design a form
based on the table for the staff member to enter each incident. I want this
information stored into the table and sent to whomever will deal with the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each infraction
will have to enter relevant information ie. date, punishement type, length
of punishment, parent contact made, brief description. This information has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two. Each
incident and Each disposition would have it own unique number (auto number)?

Thanks,
Del Dobbs


Part of the question is: will each violation involve one and only one
disposition? Or might a student have (say) detention, an essay to write
explaining why he'll never do it again, and flogging? (oops, showing my
age...)

If only one disposition will ever be needed, one table will do; if you want to
allow for the possiblity of multiple outcomes, followups, etc. then a second
table in a one-to-many relationship would be more appropriate.

--

John W. Vinson [MVP]

  #6  
Old July 3rd, 2009, 05:52 PM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default One table or Two?

You computer people speak in "computerese" (lol). I hope to be one of you
someday and be able to answer questions.


we're talking *relationships*, because Access is a relational database
management system (RDMS). you need to understand the basic principles of
relational design, so that you can structure your tables and relationships
correctly and build a solid foundation to support your queries, forms,
reports. (the common analogy is building a house: solid foundation = solid
house, shaky foundation = house falls apart and needs frequent patches,
work-arounds, partial rebuilding to correct problems, etc. = ongoing
nightmare.) i strongly urge you to read up/more on relational design
principles, then re-examine your tables/relationships and make adjustments
as needed. for more information, see
http://home.att.net/~california.db/tips.html#aTip1, and when you're ready,
go back to the site and read the rest of the tips.

to answer your immediate question: from your response, looks like there is
a many-to-many relationship between infractions and consequences. one
infraction may result in many consequences being assigned, and a single
consequence may be assigned to cover many infractions.

but frankly, your business model may be more complex that is apparent here.
are consequences codified (detention, banned from group sports, etc) or
off-the-cuff (clean the teachers blackboards - are blackboards used in
schools anymore? - do this, don't do that) or a mixture of the two? frankly,
you're in the best position to figure out the appropriate structure, *once
you've studied relational design principles*. suggest you do that, then
re-examine your tables/relationships, then post to this newsgroup with
specific questions you may have. or you can post your new
tables/relationships with an explanation of your business model, and ask for
feedback.

as for your concern about forms and users, don't worry about it. get the
basic structure right first; Access has powerful tools to build a user
interface (forms, reports, queries) and there's a way to build pretty much
whatever your users will need to handle the work flow. folks here can help
with specific issues that may arise in building the interface, once the
foundation is set.

hth


"DUNNER7" wrote in message
...

You computer people speak in "computerese" (lol). I hope to be one of you
someday and be able to answer questions. After reading your reply several
times, I think I see what you are saying.

Student could have a minor infractions (discipline incident) be assinged
detention (consequence incident) 1:1 Student could blow-off detention and
need stronger consequence. Does that get added to the original infraction

or
does that incident of "blowing-off" detention get assigned a new
consequence...meaning the first incident was never technically resolved?
Right?

Or student could come down to your office after 3 seperate incidents and

you
just get to them before the day ends and assign one consequence to all 3
incidents.
Many:Many?

So I guess you are recommending the two table approach one for discipline
incidents and one for the dispositions?

Then if I was to run a report for a student's discipline history I would
need to do a query to take the relevant infraction information and pair it
with the disposition information to form the report. Correct?

I was designing my discipline table and it goes like this:

DisciplineID (pk) auto
StudentID (lookup student ID in Demographics table)
Violation (lookup violation table)
TimeOccured (date/time)
Location (lookup in location table)
ReportedBy (lookup in staff table)
Narrative (memo)

Does that seem right should I use all of the lookups from other tables?

Thanks,
Del Dobbs

"tina" wrote:

well, you need to analyze your real-world process. will a single

instance of
an infraction ever result in more than one consequence being assigned?

for
instance, what if detention is assigned, and perhaps started, but not
completed for some reason? and will multiple infractions ever be

assigned a
single punishment? you'll know the possible variations best, of course.

once you've defined the situations that the table structure needs to
support, then define the relationship between infractions and

consequences:
is it one-to-one, one-to-many, or many-to-many? when you've defined the
relationship, you'll know what table(s) you need to support it.

just a tip: you'll probably want to find a middle ground between what
happens most of the time, and what could *conceiveably* happen. it's not
realistic to build a structure that will support EVERY possible

scenario;
but you need to draw the line at the point where you can see a

reasonable,
workable way to "squeeze" or "tweak" highly unusual real-world events a

bit,
so they fit into the standard parameters you've defined.

hth


"DUNNER7" wrote in message
...

I am progressing with my student database, thanks for all help so far,

my
next problem is this. I want to record student discipline

infractions.
This
means that I will have a table to record each individual incident as

reported
by a staff member and the table will contain basic info, ie. time,

location,
rule violated, brief description of event. I will probably design a

form
based on the table for the staff member to enter each incident. I

want
this
information stored into the table and sent to whomever will deal with

the
infraction & cc. to me (principal).

Person who deals with the actual consequence/punishment for each

infraction
will have to enter relevant information ie. date, punishement type,

length
of punishment, parent contact made, brief description. This

information
has
to be sent back to the referring staff member and stored in a table.

Question is: Do I need two tables? ex. 1: "Violation" and 2:
"Disposition"? Or can I have one table: "Discipline" and each party

staff
member and disciplinarian can enter their data piece into the specific
incident on the one (1) table?

If I need two tables the studentID would be the link between the two.

Each
incident and Each disposition would have it own unique number (auto

number)?

Thanks,
Del Dobbs






  #7  
Old July 4th, 2009, 01:45 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default One table or Two?

On Fri, 3 Jul 2009 08:13:01 -0700, DUNNER7
wrote:

Thanks, you take me back to my original question. If I am able to do this
with one table (infractions/dispositions) together.... can two seperate
people, 1: the reporer of the incident and, 2: the person who handles the
disposition enter data into the same table using two different forms.


Yes.

A form is just a tool, a window. It will update records in the table that is
being used as its Recordsource.

You can easily have two different forms bound to the same table.

--

John W. Vinson [MVP]
  #8  
Old July 6th, 2009, 12:50 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default One table or Two?

On Fri, 3 Jul 2009 08:13:01 -0700, DUNNER7
wrote:

Thanks, you take me back to my original question. If I am able to do this
with one table (infractions/dispositions) together.... can two seperate
people, 1: the reporer of the incident and, 2: the person who handles the
disposition enter data into the same table using two different forms.


Access is multiuser, out of the box. Two users can use the same database,
concurrently or separately.

One Table can be referenced by two (or twenty) separate forms.

So... Yes.


For
example I would design form that would enter specifics of the
infaction...form would be sent to admin (disposition part), person could view
infraction, hit command button, pull up form to enter disposition, hit enter,
and disposition information is entered onto same row as infraction
information in the fields set aside for disposition data in one table??


Yes.

It would be nice to be able to flogg a child every once in awhile.


Well, not *really* but... I know the feeling!

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.