Database Normalization – How to Normalize Database with Multiple Products to Single Transaction ID

I would leave the data in a single table. Actually, the data is normalized now in the sense that you have a single Person table. That person is identified by a name, and the remaining columns are data about that Person. Each column depends on the name and varies with the name, which means it is already in 3NF. If you were building a large application, say a corporate application, then it might make sense to create tables for email, phone, address, etc. But since this is just a simple mailing list I would keep it one table. I don't think you have value in having a table for addresses or churches, for example, when neither of these have meaning to you other than as they relate to the current person.

Now if you had do have a lot of data about that address, or about that church, for example, then these "entities" would need to become tables to store the data about each address or church. Say your mailing list has the church name, church address, pastor, and average attendance. Now you have a reason to create a church table. You have columns - address, pastor, average attendence - that depend only on church name, not person name. But if all you have is church name there is really no benefit in this case of creating a church table that has just an id and a name.

One area where you might normalize would be to remove the repeating groups - the email(s) and phones(s) - into child tables. But you could also create multiple columns - say 3 for each - and I'm sure that would be plenty for this application. While not normalized, the only downside in doing this is if you need to store 4 (unlikely, and this is just a mailing list) you are out of luck. It also makes the query more complicated in that if you want to return all the phone numbers you have to select each column instead of joining to the child email table.

Bottom line is that normalization is about removing redundancy. It sounds like you have very little redundancy in the mailing list to begin with and breaking individual columns in the current mailing list into their own tables, if there is are no columns for those tables other than an id and a name, would be overkill.

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables:

CREATE TABLE dbo.Users
(
  UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users
  Username NVARCHAR(128) NOT NULL UNIQUE,
  /* , other columns */
);

CREATE TABLE dbo.Tables
(
  TableID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 tables
  Name NVARCHAR(128) NOT NULL UNIQUE
  /* , other columns */
);

This auditing table assumes that all of the audited tables have an INT primary key (or a PK that will fit into INT). This will obviously be more complex if you have different data types, or compound primary keys, in which case you may just consider different auditing tables - still more valuable, IMHO, than only keeping the last modification for any one row.

CREATE TABLE dbo.AuditLog
(
  TableID       TINYINT NOT NULL 
                FOREIGN KEY REFERENCES dbo.Tables(TableID),
  ID            INT, -- loose reference to entity table's PK
  Action        CHAR(1) CHECK (Action IN ('I', 'U', 'D')),
  UserID        TINYINT NULL -- just in case
                FOREIGN KEY REFERENCES dbo.Users(UserID),
  EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

(Note that I intentionally did not dictate what the clustered index should be, since I don't know all of your query patterns aside from the one mentioned in the question. It is quite likely that you will want to cluster leading on EventDateTime, especially if you are going to query for events that happened recently, and/or you are going to purge data periodically, which is never a bad idea. This will at least ensure that new rows are added to the "end" of the table instead of page splits happening all over the place if you choose something like TableID as the leading column.)

Now your trigger can check the user name (not sure about your authentication method, but this may be via SUSER_SNAME()), it knows what table it's dealing with, it can determine the action, so it just has to add the ID(s) from inserted/deleted. Hokey example that assumes a table named dbo.foo with a primary key called FooID:

INSERT dbo.tables(Name) SELECT N'dbo.foo';
GO

CREATE TRIGGER dbo.AuditFoo
ON dbo.foo
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @UserID TINYINT, @TableID TINYINT, @now DATETIME;

  SELECT @UserID = UserID -- could be NULL
    FROM dbo.Users WHERE Username = SUSER_SNAME();

  SELECT @TableID = TableID 
    FROM dbo.Tables WHERE Name = N'dbo.foo';

  -- inserts
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'I', @UserID
    FROM inserted AS i WHERE NOT EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- updates
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'U', @UserID
    FROM inserted AS i WHERE EXISTS
    (SELECT 1 FROM deleted AS d WHERE d.FooID = i.FooID);

  -- deletes
  INSERT dbo.AuditLog(TableID, ID, Action, UserID)
  SELECT @TableID, FooID, 'D', @UserID
    FROM deleted AS d WHERE NOT EXISTS
    (SELECT 1 FROM inserted AS i WHERE i.FooID = d.FooID);
END
GO

Only one of those inserts will fire per statement / trigger invocation (well, to be pedantic, the trigger itself will fire multiple times in the event of MERGE).

If you don't want to keep a list of users / tables you can always populate this dynamically (though if you're not strictly controlling the list you may seriously reconsider the TINYINT suggestion above). E.g. to capture users you're seeing for the first time and haven't manually inventoried:

  SELECT @UserID = UserID FROM dbo.Users WHERE Username = SUSER_SNAME();

  IF @UserID IS NULL
  BEGIN
    INSERT dbo.Users(Username) SELECT SUSER_SNAME();
    SELECT @UserID = SCOPE_IDENTITY();
  END

But for the table this doesn't make much sense. Just add the row when you create the trigger, and you can even hard-code the TableID instead of deriving it at runtime.

Once you are collecting data in dbo.AuditLog, you should be able to easily derive information about the last action of any specific type, restricted to a table, user, or even individual entity. If you need help constructing any of those queries, please start a new question with the schema, some sample rows and desired / expected results. Using SQLFiddle can be quite helpful.