CODEWITHSUNDEEP
mysqldatabasedatabase-design
oym
oym

Reputation: 7083

Help with database design

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!

Upvotes: 0

Views: 157

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

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.

Upvotes: 5

Chris Kannon
Chris Kannon

Reputation: 6091

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)      
)

Upvotes: 3

Related Questions