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).
  • So all you want to do is enforce uniqueness? Nov 14 '11 at 6:40
  • @JackDouglas: Yes; there might be some mining of these tables later for data, but this composite key will not be a part of that (it will be related to other data on other attributes).
    – casperOne
    Nov 14 '11 at 13:00

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

  • I'm not sure this is a good route for me; the goal of the structure is to be flexible enough to accommodate different ways that namespaces/domains identify their content. From what I can tell by the linked answer I have to have a table per data type I want to use as the natural key in the domain; what does this gain me except for many extra tables when a sql_variant does the same job? I've updated my question to reflect this (last item in the "other key facts setion").
    – casperOne
    Nov 14 '11 at 0:52
  • IMO you will end up regretting it. If you don't want/need extra tables then a bunch of extra columns and perhaps a functional index would be far preferable Nov 14 '11 at 6:44
  • I can appreciate the possibility of regretting it, but I can't understand it when the statement is so vague. Would you elaborate on why? What will a sql_variant as part of the key (given the updated information) do to post a problem?
    – casperOne
    Nov 14 '11 at 12:59
  • Both of which are not issues which I'm constrained on. I appreciate future-proofing, and I can't say this will never be a condition, but at the same time, I'm fairly confident for what I'm trying to do, this will not be an issue ever, or for a long, long time.
    – casperOne
    Nov 14 '11 at 13:11

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.

  • The PostId is going to be used to uniquely identify a post within a namespace/domain, there will be a synthetic id generated as the primary key, this will be used for constraint/indexing purposes.
    – casperOne
    Nov 14 '11 at 0:38
  • Updated answer to reflect the non-query use of the key. It's not going to be used for anything really beyond the constraint.
    – casperOne
    Nov 14 '11 at 0:56
  • If the constraint you're talking about is a unique constraint, built on the combination of BlogId and PostId, then using a string representation of PostId is likely to be as useful as any other representation - and probably a lot more convenient, since it will no doubt be coming to you over http as a string to begin with.
    – Joel Brown
    Nov 14 '11 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.