CODEWITHSUNDEEP
sql-serverdatabase-designmany-to-many
Taryn
Taryn

Reputation: 247670

Database Design for Many To Many Relationship

I am in the middle of rewriting an MS Access database and unfortunately we need to keep some of the strange processes that exist in the current DB. I am having some trouble doing the DB design so I am hoping for some advice/direction.

New tables:

tblCurrentDayRecords
BusinessDate (PK)
Acct (PK)
ActivityDate (PK)
Type
RecordGuid

tblNotes
RecordGuid (PK)
NoteGuid (PK)
Note
NoteDate
NoteAddedBy

Data gets added to the tblCurrentDayRecords each morning. These records are then processed in the UI. When processing the records, notes are added which relate back to the records using the RecordGuid.

The problem I am having with the design is that each day we get new records added to the tblCurrentDayRecords table but the same account might appear and if it does, then the notes from the previous record must be bridged forward from day to day. So the process goes like this:

Day 
5/30 Acct 123456789 Note1, Note2
5/31 Acct 123456789 Note1, Note2, Note3
6/1  Acct 123456789 Note1, Note2, Note3, Note4, Note5

So I need to move the notes from the previous day to forward if the account is added each subsequent day. Each of these records will have a new RecordGuid that gets generated when it gets added to the tblCurrentDayRecords table.

I am open to changing the design of the Notes table using a Join table, whatever to get this to work but I cannot figure out the best way to do this without having to run an Insert Or Update each day to tie the new record to the previous notes. Let me clarify and say that I cannot just update the Notes table with the new RecordGuid because the notes still need to be accessible to each day of records.

I hope this explanation is sufficient to get some feedback or advice on how to proceed.

Upvotes: 0

Views: 174

Answers (1)

King Skippus
King Skippus

Reputation: 3826

You kind of lost me in your description, but I can tell you that in general, for a many-to-many relationship, you will need to have a table that relates the rows in table 1 to the rows in table 2. Is this what you're looking for?

tblRecordNotes
  BusinessDate (PK)
  Acct (PK)
  ActivityDate (PK)
  RecordGuid (PK)

Then processing your sample data,you would perform the following:

INSERT INTO tblRecordNotes VALUES ('2011-05-30', 123456789, '2011-05-30', 'Note1');
INSERT INTO tblRecordNotes VALUES ('2011-05-30', 123456789, '2011-05-30', 'Note2');
INSERT INTO tblRecordNotes VALUES ('2011-05-31', 123456789, '2011-05-31', 'Note1');
INSERT INTO tblRecordNotes VALUES ('2011-05-31', 123456789, '2011-05-31', 'Note2');
INSERT INTO tblRecordNotes VALUES ('2011-05-31', 123456789, '2011-05-31', 'Note3');
INSERT INTO tblRecordNotes VALUES ('2011-06-01', 123456789, '2011-06-01', 'Note1');
INSERT INTO tblRecordNotes VALUES ('2011-06-01', 123456789, '2011-06-01', 'Note2');
INSERT INTO tblRecordNotes VALUES ('2011-06-01', 123456789, '2011-06-01', 'Note3');
INSERT INTO tblRecordNotes VALUES ('2011-06-01', 123456789, '2011-06-01', 'Note4');
INSERT INTO tblRecordNotes VALUES ('2011-06-01', 123456789, '2011-06-01', 'Note5');

Upvotes: 3

Related Questions