0

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!

Improve this question
5
  • "But I'd like to have a notion of a globally unique Id for certain reasons" And those reasons are ? Commented Feb 2, 2010 at 20:24
  • For some front-end stuff I'm doing I'd like to be able to treat each object (article, subarticle, or comment) generically as just an "object". To do this, I'd like each object to have a uuid. Commented Feb 2, 2010 at 20:26
  • Can you provide some details? It sounds like you might be making more work for yourself than is necessary... Commented Feb 2, 2010 at 20:32
  • Just to make the question simpler: assume that I have multiple tables with PK/FK relationships. On the client side these will be rendered similarly and will be ID'd (for javascript stuff) by their unique id's. If the Id's are not globally unique I run into problems.. Commented Feb 2, 2010 at 20:43
  • What problems? You know what table the data came from when selecting it, why not just pass this on the presentation layer, so that you can use it when displaying or rendering code to make updates to the table in question? Commented Feb 2, 2010 at 21:19

2 Answers 2

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1 the top-most article's ID is unique enough - all subarticles and comments are bound to it - and of you are not calling comments from outside - IDs from one table are always unique.
@OrbMan - not quite, part of his question was "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?"
@Chris - he did not specify a unique ID across all entities (until a later comment). I assumed he meant between Article and SubArticle, and this schema achieves that.
@OrbMan - He did, he specified globally and UUID, which is UNIVERSALLY unique id.
@Chris - UUID was not a requirement, it was an implementation suggestion. I interpreted his need as being able to uniquely identify articles and subarticles without overlapping PKs, which I don't think was an unreasonable supposition based on the original question, despite the appearance of the words global and uuid. It is often the case that you need to read between the lines and try to determine what the business need really is. I am still not convinced of the need for a UUID in this case.
3

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) )
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.