Help with database design

Question

I'd like some advice designing my database tables for a small project I'm working on. Assuming in this systems I have articles, subarticles, and comments.

Each article can have subarticles. Both articles and subarticles can have comments. I considered having an autoincrementing int primary key for each table (i.e. articleId, subarticleId, and commentId). Subarticles would have an articleId as a foreign key into the Article table etc.

But I'd like to have a notion of a globally unique Id for certain reasons. What would be the best way to implement this? Should I have a uuid primary key in each table and use the previously mentioned Id column as just a regular column (since I still would like a logical number associated with each object)? Or should I make some sort of main object mapping table containing the uuid?

Any suggestions on good ways to implement this would be appreciated!

Answer 1

I would just have the tables Article and Comment, and the Article table would have a NULL-able ParentArticleID field.

Article
-------
ArticleID (int PK)
ParentArticleID (nullable int FK)
...

Comment
-------
CommentID (int PK)
ArticleID (int FK)
...

If you do need a GUID, do not use it as the PK because it will not perform as well when indexing. Make separate fields for the GUIDs.

Answer 2

Keep it simple.

If you absolutely have to have UUID, don't make it the primary key. It's complex and hard to keep track of since you also want to have a unique numeric identifier. Just have a seperate UUID field if you need it.

So, now you have Article and Comment (subarticle is just another Article, no?). Article has article_id, Comment has comment_id. Both identity columns.

Pseudo Defs in Transact SQL
table Article (
    article_id bigint identity not null
,   parent_id  bigint null   --populate for child or sub articles
,   object_id  uuid not null
,   constraint article_pk primary key (article_id)
)

table Comment (
    comment_id bigint identity not null
,   article_id bigint not null --assuming comments MUST have an article YMMV
,   object_id  uuid not null
,   constraint comment_pk primary key (comment_id)
,   constraint comment_article_fk foreign key (article_id) 
    references Article (article_id)      
)