| Author |
Topic |
|
ndn_24_7
Starting Member
23 Posts |
 Posted - 2004-12-22 : 11:40:32
|
Hello all,I’m trying to develop an incident reporting application with an SQL 7.0 backend and an Access 2000 front. I migrated all the old data from the old application into SQL 7.0. When I transferred all the old data and the tables into SQL, Everything was stored in a single table. I’m trying to break up the table into several different tables each describing on thing. This is proving to be a little difficult. So instead of trying to break it up, I’m just going to add some tables then start pointing data to go to those tables. The problem I’m having is how to relate everything to the old table. I want to add a Suspect table to the database. For every incident, there could be one or more suspects. My incident and suspect tables looks like this:Incident Table ID Department incident datereport timereport dateoccur timeoccur location complaintant description usercode reportedby disposition NarrativeSuspect Table [Suspect ID] [First Name] [Last Name] [Middle Intial] Address1 City State Zip DateofBirth Race Sex Height Weight EyeColor HairColor Picture My question is how should I relate these two tables in the database so that one incident can have many suspects. And how do I make the Access form point only to suspects that are involved in that one incident. I want to make a button that a user can click and see which suspects are involved in that incident. I’m not sure if that’s done through the relationship or in the form it self.Any assistance will be greatly appreciated.
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-22 : 13:27:08
|
| To define a one to many relationship from a Parent table (Incident) to a Child table (Suspect), add the Parent Key to the Child table and define a FOREIGN KEY constraint on the Child table that references the Parent table.alter table Suspectadd IncidentID intgoalter table Suspectadd constraint FK_SuspectIncident foreign key (IncidentID) references Incident(ID)Having said that, do you want to create a 1-to-Many relationship or a Many-to-Many relationship? One Incident could have many suspects and one suspect could be involved in many incidents. A Many-to-Many relationship is defined by creating a third table, called a junction table. This table can consist of nothing more than the keys from each of the two tables. Other columns can be added if they are relevant.Create table IncidentSuspect ( IncidentID int, SuspectID int,constraint PK_IncidentSuspect primary key (IncidentID, SuspectID))goHTH=================================================================Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910) |
 |
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-22 : 14:01:37
|
| Do i have to populate the junction table with incident and suspect ID's? |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-22 : 14:35:32
|
| when I try to insert values into the IncidentSuspect junction table, I get an error saying cannot insert null values into SuspectID colunm. since the Suspect table is a new table with nothing in it yet, there is going to be a blank colunm for all the suspect ids until values are put in.How do I get around this. Should I remove SuspectID as a primary key, then transfer the incidentID over then create some dummy suspect ID's then reestablish the primary key to suspect ID? |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-22 : 14:36:42
|
quote:
Originally posted by ndn_24_7
Do i have to populate the junction table with incident and suspect ID's?
As Steve mentions - a junction table is used in a many-to-many relationship. Do you have a many-to-many relationship or a one-to-many? |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-22 : 15:33:35
|
| I created a many to many relationship. I removed the primary key from suspect id then inserted the IncidentID values. I then set the suspect id as an Identity that increments by 1. Then reset the primay key to suspect ID. I think this will solve the problem until I get some real data in the IncidentSuspect table. The problem I'm having now is how to update the IncidentSuspect table when the parent table is updated. I know that cascade update will do it but I dont think I have that option in SQL Server 7.0. Does anyone have any suggestions on how to cascade update the IncidenSuspect table when the Incident table is updated? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-22 : 17:39:03
|
quote:
Originally posted by ndn_24_7
when I try to insert values into the IncidentSuspect junction table, I get an error saying cannot insert null values into SuspectID colunm. since the Suspect table is a new table with nothing in it yet, there is going to be a blank colunm for all the suspect ids until values are put in.How do I get around this. Should I remove SuspectID as a primary key, then transfer the incidentID over then create some dummy suspect ID's then reestablish the primary key to suspect ID?
You can't relate anything until it exists. The normal temporal flow would be:1) An incident is reported ==> INSERT into the Incident table.2) A suspect is identified ==> INSERT into suspects if not already existing. (Also known as, "Round up the usual suspects"  )Now you can relate the incident to a suspect or suspects by making an entry or entries into the junction table. Without a Suspect, the entry in the junction table is meaningless. The LACK of an entry in the junction table for this incident means that we don't have a suspect, yet.HTH=================================================================Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-22 : 18:07:13
|
| The question is -- can a single suspect be involved in more than 1 incident? (this is what Ehorn is asking)if yes, then you have a many-to-many, and should do as Bustaz describes.if no, then you simply need to have an "IncidentID" column in the suspects table. That will related suspects to the incident to which they are involved.- Jeff |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-23 : 11:37:30
|
| How would I update the Suspect table with the IncidentID when ever something is entered into the Incident table. For example, I have a Access form the user is to fill out when an incident happens. On that form is a button that opens the suspect form. When ever the user opens the suspect form, I want just that suspect or suspects involved in that one incident to show. Right now it opens the entire table, so I have to cycle through to get to the suspect involved. I inserted the IncidentID into the Suspect table, but I cant get the field to update in the suspect table when ever I enter a incident in the incident form. Any assistance will be greatly appreciated. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-23 : 12:11:23
|
| you need to answer our question first about the relationships between suspects and incidents before we can help you. re-read our questions carefully and let us know.- Jeff |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-23 : 12:41:25
|
| A suspect can be involved in more than one incident. I created the junction table with IncidentID and SuspectID as the primary keys.I inserted the IncidentID's from the Incident tables, but I have no suspect ID's yet because it a new table with nothing in it yet. When I tried to save, it gave me an error stating cant insert null values in SuspectID because its a primary key. I dont know how to get around this. So I created some dummy entries that incremented by one so there would be somthing in the suspect ID field. I then created the relationships to IncidentID and Suspect ID to the junction table. I selected the Cascade Update related Fields option. I thought this would update the junction table and the SuspectID table with the correct IncidentID when ever the Access form was filled out and submitted. But none of the tables are updating. This is where im stuck at. An incident can have more the one suspect. I need to be able to tie those suspects to a single incident. Does this make sense? Let me know if their any other questions. I'm a rookie DBA, so if there is anything i'm missing please let me know. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-23 : 12:56:35
|
| Why are you adding rows to the junctiont table if there are no suspects yet on an incident? you simply don't add rows to that table until there is a relationship to define. As mentioned, the order of operations is: add incident, add suspect, relate the two. You can't relate the two until you have both. does this make sense?Other than that, it sounds like you are having problems with Access forms, so you post specific questions in the Access forums.- Jeff |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-23 : 13:20:29
|
| When I follow the steps add incident, add suspect, are the tables suppose to update automatically? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-23 : 15:05:16
|
| There are two things going on here -- your table design and your user interface.As far as storing the data in the tables, before a row can be added in the table that relates indicents to suspects, you must have entries in the Incident table and in the suspect table. Does this make sense? If not, stop reading here and ask specifically about this. Do not confuse this notion with ANYTHING that anything to do with forms, data entry, MS Access, VB, websites, or anything else.Once that is understood, now it's time to think about your application and how you are going to populate the tables. All we can do is guess as to what you are doing here unless you tell us specifically what you are trying. But no application is going to "automatically" add rows to tables for you, you must tell the computer what you want it to do. Your application or MS Access forms or whatever your user interface is can do whatever you want, but it must add the data to the tables in the sequence mentioned. Hopefully, it is clear WHY you cannot relate an incident to a suspect if neither exists yet. Does this make sense?- Jeff |
|
|
|
ndn_24_7
Starting Member
23 Posts |
Posted - 2004-12-27 : 12:32:14
|
| yes, this make sense. I guess im just confused on how to populate the junction table from a single entry. After thinking about it, My logic was way off. I was thinking if i make the relationships cascade update, the table would update automatically when i enter both incident and suspect. I guess this was wishful thinking on my part. thanks for all your help, I will continue to work on this and figure out how to make it work |
|
|
|
|
Table Relationships