Need to see if this will setup will work (1 Viewer)

[rhett7660]

Help with database design....

Hello..

I am putting together a database that will track incidents for our city on the 4th of july...And I started to read about normalization (this is long..please bear with me...)

Here is how I have it setup.....

tblMain
IncidentNumber (PK)(AutoNumber)
IncidentDate
TagNumber
Address
CityStreet (lookup table that has all the street names)
City (look up table with main and surrounding cities)
ZipCode
RD
PaveAssociated (yes/no)
PaveNumber (lookup table with pave numbers)
Source (lookup table for source)
FireworkType (lookup table for firework source)
LocationType (lookup table for location type)
LastUpdated
CaseClosed
CaseClosedMemo
Completed Yes/No
Teams (lookup table of assigned team who is inputing the incident)

tblInformantInfo
IncidentNumber (setup relationship with incident number from tblMain)
Informant (yes/no)
InformantAnnon (yes/no)
TypeOfInformatant (lookup table for TOI)
LName
FName
Note

(this one we can have more then one person we contacted etc..can I have it setup like this?)

tblActionTaken
IncidentNumber (setup relationship with incident number from tblMain)
ActionTaken (lookup table for action taken)
LName
FName
2LName
2FName
Note

(this table I am thinking I might get rid of and put booking/cite etc on the main table...seems like I am entering some info twice ie names etc)

tblArrestHP
IncidentNumber (setup relationship with incident number from tblMain)
LName (there could be say up to ten people how would I handle this part?)
Fname
MInitial
Booking (yes/no)
BookingNumber
Cite (yes/no)
CiteNumber
Classification
Type
Note

All of these table are connected to the tblMain via the incident number ( I am hoping I am doing that part right.........) Also my thought process for the note and names was we could have an info from an informant that leads to contacting another person that leads to a cite/arrest etc..

Question:

Did I set this up the table structure correctly if not.. what would you do differently?

Question:

How can I setup a form for data entry when I have so many other forms that are linked to the main. When I pick all the tables for the data entry..when I preview it, the form is blank?

I am still learning and any help would greatly appreciated!

Thanks again
R~

 

[rhett7660]

Anybody have an suggestiongs?? Tips??....

Thanks again
R~

 

[The_Doc_Man]

Without knowing the business rules to be applied (and no, I don't WANT to know them), it would be hard to be sure that you are set up right, wrong, or indifferent. I often give this next advice and here more than ever, it makes sense for what you asked.

Get a big dry-erase board, some appropriate markers, and some sticky-note pads. Name your tables on the board. Now populate them with notes.

Make a list of things you want to do. Do this on something as simple as a legal pad. You'll use lots of paper for this exercise, though, so don't be afraid to buy a large pack of same and a bunch of pencils and a good sharpener.

For every item you list, look at the tables you have laid out. See what you have to do to accomplish your task. See what tables/fields you must touch to do the required thing.

The rules for populating fields in a table are:

a. Never put two of the same thing for the same purpose in the same record in the same table. (Repeating group rule). E.g. Do not put two names for interviews on the same incident. Instead, have a table of interviewers. A table of interviewees. A table of interviews that provides the junction between interviewer, interviewee, and incident number. You have as many interview records as it takes to identify all interviewees separately.

b. Never put two different types of things in two different records of the same table. (Apples and oranges rule) E.g. Don't put interview information in an incident table. Instead, keep them separate. Identify their relationship via pointers. This interview relates to this incident. THAT interview relates to THAT incident. etc. etc.

c. When putting something in a table, be sure that the table keys (if any) relate to what you are putting there. E.g. an interview occurs on a date and relates to an incident. You would identify the interviewee by foreign key relationships but would not put the interviewee's date of birth in the interview table. It doesn't depend on the date of the interview. You would put DOB in the interviewee table.

Having said that, look at this from your original post...

tblActionTaken
IncidentNumber (setup relationship with incident number from tblMain)
ActionTaken (lookup table for action taken)
LName
FName
2LName
2FName
Note

(this table I am thinking I might get rid of and put booking/cite etc on the main table...seems like I am entering some info twice ie names etc)

You have a repeating group that needs to be converted to a "parent/many children" layout. Don't do this.

It is hard to know what you wanted to do and since it appears to be related to something rather official, I'm not up enough on your arrest procedures to be able to give good procedural advice. I can only offer guidelines on how to decide you have a bad layout given your rules that only you will know very well. For us it would just be a guess.

 

[rhett7660]

Doc...

Thank you for taking the time to look at what I setup.... I am going to take your advice and do some writing/erasing..... I have a few more questions that you brought up///

You have a repeating group that needs to be converted to a "parent/many children" layout. Don't do this.

The don't do this part of the above statement... Are you saying don't do what I have done..ie 1Lname....2LName...etc?? Or are you saying don't do the parent/many children part?? Just a little confused on the statement..

It is hard to know what you wanted to do and since it appears to be related to something rather official, I'm not up enough on your arrest procedures to be able to give good procedural advice.

This is just something the city wants to track....... I had this on a spreadsheet orginially... and this was getting way out of hand..lol.. So now I am putting it in a database.......

I originally had everything in one table with the exceptions of the lookup tables.... Once i started to read about normalization I started to break it down.....

All of the tables that will hold info (not look up tables) are linked via the Primary key... Is the correct way of doing this??

IE Incident #1 has a cite has in interview has an informant...... hence the reason for having the name/address fields in multipal tables because you could have a call for service with multipal informants/multipal cites.. I don't know how to set it up to show incident #1 had 3 inf/5 cites/and here are the names of all the parties involved.. hence the 1Lname...2Lname..etc...

Now once this is setup how can the end user input the data for incident #1 with 3 inf names 3 inf address/ 5 cites/ 5 cite address?? That is the part that is also very confusing for me...

Thanks again for your help and insight into this...

R~

 

[workmad3]

Doc...

Thank you for taking the time to look at what I setup.... I am going to take your advice and do some writing/erasing..... I have a few more questions that you brought up///



The don't do this part of the above statement... Are you saying don't do what I have done..ie 1Lname....2LName...etc?? Or are you saying don't do the parent/many children part?? Just a little confused on the statement..

If you need multiple first and last names in a record, you should do it through a linked table, rather than having 1, 2, 3 etc in a table. Doing it the way it is at the moment violates 1st normal form, as some records will have 1 name, some will have 2 making the records in the table have different lengths.

This is just something the city wants to track....... I had this on a spreadsheet orginially... and this was getting way out of hand..lol.. So now I am putting it in a database.......

I originally had everything in one table with the exceptions of the lookup tables.... Once i started to read about normalization I started to break it down.....

All of the tables that will hold info (not look up tables) are linked via the Primary key... Is the correct way of doing this??

table should be linked with primary key of one table being a foreign key in the other. If the other table has a weak relationship, then the primary key should also be part of the composite primary key in the linked table.

IE Incident #1 has a cite has in interview has an informant...... hence the reason for having the name/address fields in multipal tables because you could have a call for service with multipal informants/multipal cites.. I don't know how to set it up to show incident #1 had 3 inf/5 cites/and here are the names of all the parties involved.. hence the 1Lname...2Lname..etc...

Now once this is setup how can the end user input the data for incident #1 with 3 inf names 3 inf address/ 5 cites/ 5 cite address?? That is the part that is also very confusing for me...

Thanks again for your help and insight into this...

R~

Well, if the names are moved out into a different table, then you simply have that table linked to all the relevant tables

Hope this has helped a bit

 

[rhett7660]

Thanks for the tips....

One more question.. when doing a lookup table.... I have the following:

tblMain
...
...
City (lookup via tblCities)

tblCities
CityID
City

When you use the wizard is it better to just have the table store the CityID or should I have it do both the CityID and City?? I want it to just store the number (cut down on the amount of info in the main table). Does this make sense??

Will this have an effect on any reports/queries/forms I do in the future?? If I am only storing just the "CityID"??

Thanks again for you help!
R~

 

[RV]

City (lookup via tblCities)

If you're saying that yoy've got a lookup column, don't use lookup column.
Put your cities in a list box on a form and store CityID in your main table.

I advse you to reconsider your data structure.
Right now you've got what I'd call a "spider" structure (one maio table that relates to other tables whereas the other tables only relate to the main table).
That's a common example of an incorrect data structure.
Better to start from scratch using the approach as suggested by The Doc Man.

RV

 

[rhett7660]

RV...

If you're saying that yoy've got a lookup column, don't use lookup column.
Put your cities in a list box on a form and store CityID in your main table.

If the table is not a look up table how do I get the CityID from a drop down combo box on a form?? Just want to make sure I am reading this correctly...

What I planned on doing is having a lookup table called tblCity. Within that table I have
CityID
Cities

What I want to be able to do is have the cities show up in a form and have the CityID stored in the main table. Just have the number...

So instead what you are saying is just have the table then via a form have the cities drop down but have the form store the cityID in the tblMain?

What would be the benifit of this? I don't know, hence the reason for the question? Does it make the database faster?? etc.....

I advse you to reconsider your data structure.
Right now you've got what I'd call a "spider" structure (one maio table that relates to other tables whereas the other tables only relate to the main table).
That's a common example of an incorrect data structure.
Better to start from scratch using the approach as suggested by The Doc Man.

RV

I agree..... but when I put all the info that I would like to collect on paper.....I don't know how I can relate it back to the tlbMain without using a pK in each table in order to relate it.... I am still very new to the concept of normalizing a database..

On another note are there any good books that talk about how to build a database within access....that talks and shows you how and why??

Thanks again for your help!!
R~

 

[workmad3]

Spider structure is often, but not always a bad layout for tables. My current project has a very much spider structure, but its the only logical way of doing it, as I am storing quotes in a DB and there are several sub tables for different data values in different types of quotes. If you end up with a spider style table layout, you should make sure that all the relationships are weak though. A weak relationship basically says that an entry in the weak table cannot exist without the entry in the strong table. In my case, if I removed all the quotes from the main table, everything in all the subtables would also be removed (easily enforcable using cascades )