Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DarkCloudInc
Starting Member
10 Posts |
 Posted - 2006-05-10 : 13:16:13
|
| I'm currently working on integrating two databases together and came across an issue of data consistency. There are two tables, tbl_ev and tbl_sn, with half the time being linked to one another and the other times, not. When they do link to each other, they must maintain a 'One-to-One' relationship. This instance is fine. But the other instance when they are not linked to one another, would break the ability to make no duplicates allowed as a '0' value is limited to one single record. How would I go about making this happen where when they do link, they maintain a 'One-to-One' relationship but also allow both tables the ability to not link to each other?Current Table Schema:====================Table: tbl_A--------------------Event_UID [PK]Session_UID [FK]====================Table: tbl_b--------------------Session_UID [PK] |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-10 : 22:31:26
|
you cannot do one-to-one if that is your case, you should employone-to-many, but this will break the relationship based on what you just describedhere are some questions that will help you:1. based on your table names, an event could have n-number of sessions?2. can a session exist without an event?if you are really tied up with the business policy you just described, your session_uid must be unique, therefore 0's will not do, i suggest adding another column and you can add value like 'D' for dummy or 'R' for real and append that to your session_uid which would be unique somewhat, or define values outside your normal range for session_uidor to make it easier for us to help you, post both table schema --------------------keeping it simple... |
 |
|
|
DarkCloudInc
Starting Member
10 Posts |
Posted - 2006-05-11 : 12:04:51
|
| To answer your questions:1. based on your table names, an event could have n-number of sessions?2. can a session exist without an event?Yes it can. A session can exist without an event. An event can exist without a session. However, when a session does exists with event, it must be a one to one relationship.That suggestion does make sense to introduce another column.I'll have to get back to you on that. I'm still extracting more information from the current users of both databases.Thanks for your response though.I might as post the answers I've been looking into: - Have both tables have a FK and PK refering to each other===============tbl_a---------------event_UID [PK]session_UID [FK]===============tbl_b---------------session_UID [PK]event_UID [FK]Then since the front end is Microsoft Access, I would use VBA update one another when the user so choses to do so. But the issue here is when placing an index on the FK columns, then the issue above would rise. However, the idea of a dummy value would fit here.- a Reciept like tableI was thinking of creating a table that would have three columns to it to hold the values:===============tbl_a---------------event_UID [PK]===============tbl_b---------------session_UID [PK]===============tbl_ab---------------EvSe_UID [PK]session_UID [FK]event_UID [FK]Then placing a trigger On UPDATE and INSERT on table AB or VBA to manage the data consistancy. However, I'm unsure of the amount of work that would be needed for this as it my not please my boss to spend tons of hours on it.But until I extract more information from both sets of users, I suppose all I can is speculate and find the most viable solution. |
|
|
|
DarkCloudInc
Starting Member
10 Posts |
Posted - 2006-05-16 : 11:28:01
|
Update: I finally extracted the requirements out of the users and here's the situation as it stands now.Is it better to handle the deletion of a record through VBA or through triggers?Situation in context of this event:quote:
Three Tables:tbl_Sessions--Session_UID [Primary Key] tbl_Event--Event_UID [Primary Key] tbl_SessionEvent--SesEve_UID [Primary Key]Event_UID [Foreign Key - No Duplicates]Session_UID [Foreign Key - Duplicates OK] The reason for this design:- Sessions can exist independantly from Events- Events can exist independantly from Sessions- When a Session is assigned to an Event, they maintain a One to One relationship which can only be assigned from the Event dept's Form.
The trigger for this event:quote:
If the user decided to either change/remove the session from the event, the record from the tbl_SesEve would be updated/removed.
Now the question that I am asking, is it better to quote:
Option 1Use a trigger on Update to the record if they decide to change the session; and use a trigger on Update to delete the record from tbl_SesEve if they decide remove the session
quote:
Option 2Program in VBA to update the record in tbl_SesEve if they decide to change it and delete the record in tbl_SesEve if the user decides to remove the Session
Now, I am also changing the table structure to deal with the issue behind the table data, so if you have any ideas of a design that can handle this criteria, I'm open to hearing it:quote:
Context of the design of the table structure- Sessions can exist independantly from Events- Events can exist independantly from Sessions- When a Session is assigned to an Event, they maintain a One to One relationship which can only be assigned from the Event dept's Form.
Currently, I'm unsure about the structure as it could be potentially messy.Edit: solution has been achieved. Thanks anyways. |
|
|
|
|
|
|
|
|
Maintaining a 'One-to-One' relationship