Will this Post/Tag DB schema cause problems later?

Question

I'm planning a new service for my ASP.NET MVC app and want to use tags. I've never really been too fond of tags, but SO delivers so I'll give it a shot.

Here's the simplified schema I'm thinking about:

Post Table
------------------
PK PostId BigInt (or perhaps uniqueidentifier)
...more post related fields...
Tags nvarchar(200)

Then I'd LINQ query something like:

_db.Posts.Where(p => p.Tags.Contains("TagToFind"));

Obviously, this is super simple, but would do what I need. Do you see any glaring problems with this? Likely performance, I'd imagine .Contains() isn't exactly fast.

UPDATE I plan to have a pre-built array of allowable tags that the user can pick from.

score 1 · Accepted Answer · answered Jul 13 '10 at 06:51

1

This may work, but it will not scale unless you have a fulltext index on that column (and you query it using fulltext style queries).

Also normalizing tags (having a tags table) is kind of key, unless you do that you can not deal with merges or track when / who created what tag.

answered Jul 13 '10 at 06:51

Sam Saffron

128,308
78
326
506

I updated my question. I'm going to have a pre-built array of allowable tags. As for the fulltext style query, what would that look like in LINQ? Anything special that would need to be done to the Tag field? – Chaddeus Jul 13 '10 at 07:11
@Chad see: http://stackoverflow.com/questions/224475/is-it-possible-to-use-full-text-search-fts-with-linq – Sam Saffron Jul 13 '10 at 08:39

score 0 · Answer 2 · answered Jul 13 '10 at 11:05

0

Do you see any glaring problems with this?

Well, let's give it a test:

INSERT INTO PostTable (PostId, Tags) 
VALUES (0, NULL), 
       (1, NULL), 
       (2, NULL), 
       (22, ''), 
       (55, 'Hello world!'), 
       (-99, 'Hello world!');

No relational key, no data integrity. No, absolutely no problems there ;)

answered Jul 13 '10 at 11:05

onedaywhen

55,269
12
100
138

While I appreciate a bit of sarcasm here and there... I'm not sure I follow what you're saying... The tags are controlled on the business layer (an array of approved tags the user can select). If you mean I can't change the tag text later without heavy db query/updates... I can live with that. – Chaddeus Jul 14 '10 at 02:01

score 0 · Answer 3 · answered Jul 13 '10 at 11:24

0

Your query will not be able to use index. So, more posts => worse performance.

answered Jul 13 '10 at 11:24

zendar

13,384
14
59
75

That I expected. Likely in the future I'd have to implement a full-text index and build a stored procedure to query it. Thanks! – Chaddeus Jul 14 '10 at 02:04

Will this Post/Tag DB schema cause problems later?

3 Answers3