Database Feedback

[lettyg]

Good Morning. I just completed a database, I dont know how I pulled it off
but it looks like I did with a reference book I read. Is anyone willing to
take a look at it to give me some feedback? Not sure if that can be done.

 

[Jeff Boyce]

These newsgroups provide very good support when it comes to specific
questions.

Your request, however, is much more general than the usual requests here.
While you may find someone willing to do this, it's a little like asking
someone to review your travel plans and tell you if you did well.

What, in particular, are you curious about concerning your database?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[lettyg]

This database keeps track of our Domestic Violence Cases. I have three
tables that are associated and then many lookup tables.

I have a One to many table (Case information) this is my main table that
has the Case number and other case information (id no = primary key) this is
linked to the Charges Table by ID No (that has the counts per case, statue,
sentencing etc.) and then a Victim Witness Table also linked by id no (that
has the times the victim was contacted by a victim witness specialist - this
also has other stats I am keeping track of from that specific unit). I want
to make sure i create the right relationship and join type. I know my Case
Information table should be a parent table and the charges table and VW table
should be children tables. I haven't joined them yet because i want to
choose the right join type.

Should all of my tables (except lookup tables) also be saved as a query?

Should my lookup tables be linked in the relationships window even though i
have specified in the table to pull information from the lookup table?

I want to be able to auto populate the statue, crime name, and class of
felony that goes with the charge code for each count. i am not sure how to
do that (query?)

I hope this makes sense.

 

[Jerry Whittle]

Here's what I would check first.

1. Does every table have a primary key? If not, there's a problem.

2. Do tables have repeating data such as columns like Jan, Feb, Mar, or
anything else going across? If so, there is a problem.

3. Do records have repeating data such as the same person's name, address,
phone numbers in each record? That's a problem. You mentioned "felony that
goes with the charge code for each count" that could be a big problem if you
want to put that data into another table each time. It's better to just store
the primary key in one table as the foriegn key and use a query to gather up
all the data as needed.

4. Are you using Table Level lookup tables? That's usually a problem.

5. If you open up the Relationships window are the tables linked with
Referiential Integrity enables? If not, I start getting a BAD feeling about
the database.

 

[lettyg]

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Relationships Window.jpg

I have a screen shot of my relationships table on my skydrive. Would you
mind taking a look and making suggestions.

Would you recommend to start entering data since i have all the rows and
tables i need or should i wait until i fix all these problems?

The forms seem to work good.

 

[Duane Hookom]

I would not have any spaces in any table or field names. Your use of field
names beginning with "tbl " is confusing since "tbl" is generally used to
name tables.

Your [tbl FV Case Info] is not normalized. You are storing data in field
names such as [tbl Ddly Wpn Charged]. These should be values in a table of
case charges.

I would also pull out most of the dates and create a table of case dates.
This would have the primary key from the Case table, as well as date type and
date fields. You might also add a fields for comments associated with the
date.

Sorry to be so critical but you really need to spend some time studying
normalization.

 

[lettyg]

Thank you for replying with your suggestions. I am going to take some more
time to review all the tables. Thank you!

I would not have any spaces in any table or field names. Your use of field
names beginning with "tbl " is confusing since "tbl" is generally used to
name tables.

Your [tbl FV Case Info] is not normalized. You are storing data in field
names such as [tbl Ddly Wpn Charged]. These should be values in a table of
case charges.

I would also pull out most of the dates and create a table of case dates.
This would have the primary key from the Case table, as well as date type and
date fields. You might also add a fields for comments associated with the
date.

Sorry to be so critical but you really need to spend some time studying
normalization.
--
Duane Hookom
Microsoft Access MVP

http://cid-af7c698d9d142706.skydrive.live.com/self.aspx/.Public/Relationships Window.jpg

I have a screen shot of my relationships table on my skydrive. Would you
mind taking a look and making suggestions.

Would you recommend to start entering data since i have all the rows and
tables i need or should i wait until i fix all these problems?

The forms seem to work good.