Best type to use in a composite key when one of the values varies in type

Question

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table

BlogId                  Url                         Version
int, PK, identity       varchar(2500), not null     int, not null
-----------------       -----------------------     -------------
                1       'http://site1.com/                      1
                2       'http://site2.com/                      1
                3       'http://site2.com/                      2

Then there will be a Post table:

BlogId                Url                        PostId
int, FK, not null     varchar(2500) not null     ?

For most blogs that I'm looking at, I can easily discern a PostId value that is unique for them.

For all blogs, the Url will be unique for a BlogId value. However, the Url is frequently subject to change.

However, there are a few blogs that I can't discern a good PostId value when scraping the HTML. In those cases, I can get the datetimeoffset that a post was published on. The accuracy of the publishing frequency is on the minute level.

The question is, I need to build a composite natural key (I won't use it as a primary key because I'll need to relate to other tables and don't want to carry this along to those tables) using the BlogId and the PostId but I'm not sure what I should type the PostId as. I've been thinking of two options:

• sql_variant - I could use an int for most of my sites, and that would be fine and then use datetimeoffset for the ones where I can't easily discern a unique numeric PostId
• bigint - The PostId would fit in here and I can encode the datetimeoffset as a bigint (2011-11-11 13:59 would become 201111111359)
• Or some other approach I haven't thought about.

Some other key facts:

• The Post table will only have a few thousand records in it initially (if there are more than five thousand, I'd be surprized)
• There are related tables for tags, categories, etc, which will be joined with this table (maybe a few thousand records at most)
• The growth of any of these tables won't be astronomical. Maybe a another five thousand records a year (that's being conservative on my part but you never know)
• This key will only be used to create an index/constraint, currently, I don't foresee the use of it in queries (there are other attributes that I am linking to which are of more concern).

Answer 1

I wouldn't even consider forcing data of different types into one field.

Another option:

• synthetic post_id and
• child tables for each 'type' of 'native_post_id'

There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary

Answer 2

I may be misunderstanding your intended use of the PostId. Presumably it is going to be used like a query string (or MVC-style friendly URL suffix) to retrieve or link to the original post on its home site?

If the PostId value is only ever going to be used as a string, i.e. in the context of supplying a URI made up of the concatenation of URL + PostId, then you might just as well save it as a string, because that is the only context in which you are going to use it.

Since this is just a natural key and not your primary key, as you stated, this should do the job without being too elegant.

If, on the other hand, you mean to do some other kind of processing on PostId (other than string concatenation) then you probably ought not to save PostId as a string, unless you keep some kind of partitioning attribute that tells you which internal function you need to run the string through to get a type safe value. If you are going to that much trouble, you might just as well keep multiple PostId X columns of various types to meet your needs.