New table or new column

database-design

Apologies in advance if this is a dumb question.

Background:
I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves "purging" subscriptions, they want selected subscriptions to be removed from the client admin system but remain in the database for recording purposes. We currently have around 600,000 subscriptions. They wish to "purge" 10,000 subscriptions right away and another 30,000 over the next few months, so around 6% of the subscriptions table.

What I am wondering is what is the best way to accomplish this. I have come up with two options;

a) Add a new table to the database to store the "purged" subscriptions with 3 columns, purgedId, subscriptionId which is a fk referencing the subscription, and purgedDate. Then in any sql that references the subscription table left outer join the purged table and use "where purgedId = null".

b) Add a new column to the subscription table to store the purgedDate and add "where purgedDate = null" to all of my subscription sql.

Option a) seems like a waste, to add a whole new table to store essentially one attribute.
Option b) seems like a waste, to add a column that is only used by 6% of the rows in the table.

On another note, is an outer left join the best way to exclude the purged subscriptions?

Thanks in advance for any assistance you are able to provide and once again sorry if this is an elementary question.

Best Answer

I'd go with option 3, which will require less overall work:

Make a lookup table that contains a list of purged records and dates, like in your first option
Rename your current table to something like dbo.Tablename_master
Create a VIEW named dbo.Tablename which consists of:

SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)

Now you don't need to modify any existing code, since all references to your original table go through the view instead. This will be complicated if you have any existing constraints but they should be easy to work around.

Related Solutions

Delimited values in a column value

If you're designing an OLTP database, you should really design it in third normal form initially. If, once you've done that, you find that you can make measurable performance improvements by denormalizing something and there is no other way to get those improvements, it makes sense to denormalize selectively. It does not make sense, however, to denormalize to first normal form proactively.

I don't really understand why you expect that adding a mapping table would be problematic. Yes, you'll need to join to it. But that's what a relational database was built to do-- it joins tables very very efficiently. Plus, you won't have to constantly parse the delimited string to figure out the T2 keys. And you will be able to create a foreign key that ensures that the T2 keys in the mapping table are valid and that you don't have keys in your delimited string that don't map to a valid row in T2. I don't really see the downside.

If you have a separate mapping table and you want to return a single row of data to the client with the T1 data and the T2 keys (which seems awfully suspicious-- the T2 keys are only useful if you are going to subsequently use them to query T2 in which case you almost always want to just let the database join to T2 in the initial query) you can always pivot the data in the mapping table to return a delimited string of T2 keys. I certainly wouldn't recommend that but it can be done.

Database Design – Storing Multiple Contact Addresses for a Person

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person.

CREATE TABLE ContactInformation (
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Person(id),
    ContactInfo nvarchar(250),
    ContactType char(2)
)

Populate the ContactInformation table with the PersonID, then an email address/phone number/mailing address/twitter handle/etc and mark it with a ContactType. This could be 'MA' for mailing address, 'EM' for email, "TW' for Twitter, 'TT' for Term Time as in your question.

Triggers could validate any inserts to make sure contacts don't have a TT (term time) address unless they're a student.

Hope that helps you think outside the box. It may be too hard to make these changes at this point, if you're already deep in the application.

Edit: I didn't feel I properly addressed your direct question. If I were you and making deeper changes isn't possible or feasible at this point, I would add TermTime address to the Students table.

It belongs there because 1) it only applies to Students, so having it in the Person or Contact tables (as they exist now) is less than ideal, and 2) Each student should have one so it's already part of their student record and 3) this can be done with the least disruption to the existing schema.

Best Answer

Related Solutions

Delimited values in a column value

Database Design – Storing Multiple Contact Addresses for a Person

Related Question