Creating database to accommodate multiple values in table

Other SQL Server Topics
1

I am working on converting an Access application to SQL Server. In the process I want to redesign the database and organize the data in a more logical way. Most of the data written is written to two tables, Cases and Investigations. The investigations table has an investigations ID primary key (which I understand I have to change) and a Case ID foreign key. It also has columns called RefNo and StatuteID. Both of these columns have multiple points of entry in the application because there can be multiple reference numbers and statutes that pertain to that particular investigation. I don't want to have nine rows in that table if there are three reference numbers and three statutes.

2

why not? That would be a typical design

3

It just seemed like an unnecessary amount of rows. I also thought that design violated one of the normalization rules.

It just dawned on me that I should probably have two additional tables called RefNum and Statutes with a primary key of Investigation Number and a new row for each entry in both tables. I was thinking I needed a code in the investigations table to refer to those tables but I guess I don't.

2 Likes
4

so investigations have cases or cases have investigations? what is their relationship?
you investigate a case or is it you case an investigation? :smiley:

5

Each case has an investigation. There is a case number assigned to each case when created. My PK of investigation is the investigation number and case number is the FK. I will remove reference number and statute ID from my investigation table and create two new tables for each of them where those values will be stored for each investigation related to the investigations table on investigation number.

6

You need to back up from getting to "tables" yet.

You need to do a true logical data design/modeling first, including normalization. At this point there are no "tables" or "columns", only "entities" and "attributes". Naturally you can Google for more details. ]Much better, and more informative, though, would be to get a good book on the subject.]

Modeling/normalization is difficult to do at first, but becomes easier with experience. And the value from doing this step -- rather than trying to ignore/skip it -- is incredible.

7

As Scott recommends sit down pen and paper and write down the story in plain English (or whatever your language is). like you did above without involving any technical details. Think of describing this to grandma. "Sunny boy, what country are those Foreign Key people from? They sure seem to be nice folks though."

8
  1. can a case have more than one investigation?
  2. can one investigation have people and resources attached to it?
  3. can a case have its own status or is the status derived from all of the investigations under that case
  4. can the investigations run in parallel or in sequence
    etc
9

I'd suggest combining the RefNum and Statutes table. Difficult to know for sure without knowing more about the data you want to store, but something to consider:

ReferenceID     ReferenceType
1               Statute
2               Reference
3               Statute
10

I am actually taking the MS data modeling training at the end of this month. I will look for some other resources in the meantime to brush up on the subject.

As far as the case and investigations table go that is a one to one relationship. The statutes and reference codes are not related to each other but they are both part of the investigation.

1 Like