• A huge thanks for your reply Jeff.

    Sorry for the delay in replying

    1.
    Approx 75% of current email adresses are between 20 and 30 chars but I will increase the size as you suggest.
    The original estimate is incorrect as with all projects in the early design phase things change.
    Current conservative number is 150,000 for first one off load and anything up to 150 added per day by automated process.
    Individual email addresses can also be added by a new Web app but number is unknown
    I can only see two reasons why the email address will not immutable, one is correction for an invalid address or someone changes their address (do not know if this will be an update or insert)
    Deletes are unknown at this point as retension rules are yet to be specified

    2.
    To begin with there will be 4 channels and 5 contact types (upto 20 combinations per address)
    Attrition rates for channel/contact type is unknown except as I explained before when GDPR compliance comes in to effect all channel/contact types not compliant will be deleted, this could be large or small
    Based on your observations I am now leaning towards splitting the table thus

    CREATE TABLE dbo.Preferences
     (
     EmailAddressID INT IDENTITY(1,1) NOT NULL,
     EmailAddress VARCHAR(256) NOT NULL,
     DateCreated DATETIME NOT NULL,
     CreatedBy VARCHAR(10) NOT NULL,
     CreatedSource INTEGER NOT NULL,
     UserName VARCHAR(10) NULL
     )

    CREATE TABLE dbo.Preferences
     (
     EmailAddressID INT NOT NULL,
     ChannelID INTEGER NOT NULL,
     ContactTypeID INTEGER NOT NULL,
     AllowContact BIT NOT NULL,
     GDPR BIT NOT NULL,
     DateCreated DATETIME NOT NULL,
     CreatedBy VARCHAR(10) NOT NULL,
     CreatedSource INTEGER NOT NULL,
     UserName VARCHAR(10) NULL
     )

    EmailAddress must be unique
    PK?
    Clustered?

    3.
    FILL FACTORs?

    Other Stuff
    Yes it will be audited and triggers will ONLY audit UPDATEs and DELETEs.
    Since the created date/by/source is immutable would you exclude it from the audit tables? Just considering saving space and best practice
    Is it worth considering different filegroups for tables, indexes (where possible) and audits

    ...and whether or not they will be predominately INSERTs or UPDATES
    I forsee inserts for email addresses with minimal updates (but that is a guess)
    Channel/contact data will be inserts and updates, deletes (excluding one time deletes mentioned above) are unknown at this time.

    In addition to the above there will be other tables (audited as well) but the tables above are my main concern
    One of the additional tables will contain actual PII data per person
    approx 1 million on first intial load and upto 5,000 per day
    including email address which needs to be indexed to be searchable

    If the above table change is a good solution then I will replace email address on the PII table with EmailAddressID

    The PII data will contain only one mailing address, I normally would separate mailing address into a separated table to avoid duplication but I am worried about potential updates relocating all people linked to an adrress to the new address whether intended or not
    I suppose this will down to tight control over mailing address changes

    Far away is close at hand in the images of elsewhere.
    Anon.