How to minimize/avoid data repetition and where to use compound keys ?

Question

How to minimize/avoid data repetition and where to use compound keys ?

Paul86

SSC Journeyman

Points: 75
More actions
August 28, 2018 at 1:51 pm

#415514

Hello everyone !
I am absolutely new to database development and i'm trying to build a question bank basically.
The questions have to be categorized based on Class, Subject and Topic.
Since each Class can have many Subjects and each Subject can be in many Classes I made a junction table called tb_SubjectClass with Subject and Class being primary keys.
To uniquely identify any Chapter I would need a combination of Subject and Class so I created tb_Chapters with Chapter, Subject and Class as primary keys with Subject and Class coming from tb_SubjectClass. I then repeated the same thing with tb_Topics as each topic can only be identified with a combination of Subject, Class and Chapter.
My main question now is whether this design is acceptable or completely ridiculous ?
Some side questions:
- Does making foreign keys lead to data duplication / repetition or is it just a reference to data stored in the parent table ?
- Should compound keys be avoided wherever possible ?
I have also placed the relationship diagram below. It may also be relevant to know that I am expecting around 50000 questions (with equations and diagrams) to be stored in this database.
Any help would be appreciated !
(If the post seems stupid please bear in mind that I am an absolute beginner when it comes to database development or even programming in general 🙂 )

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

heb1014 Hall of Fame Points: 3790 More actions · Answer 1

Hey Paul,

Welcome to database development. Don't worry about what people think about your questions and just let negative responses roll off 😉

I don't have a lot of time right now (pesky schedule), but I want to comment on your question regarding compound keys. I think it's very important to separate logical and physical design concepts. It's logical to enforce uniqueness in a table based on business logic. It's also equally important to build for performance (and even storage). I would not use a composite primary key because it has implications in other indexes you create. You should definitely spend some time researching that. When I need to maintain a business constraint like you have here, I create a unique index on the columns needed.

Hope you have a great day!

Paul86 SSC Journeyman Points: 75 More actions · Answer 2

heb1014 - Wednesday, August 29, 2018 7:04 AM
Hey Paul,
Welcome to database development. Don't worry about what people think about your questions and just let negative responses roll off 😉
I don't have a lot of time right now (pesky schedule), but I want to comment on your question regarding compound keys. I think it's very important to separate logical and physical design concepts. It's logical to enforce uniqueness in a table based on business logic. It's also equally important to build for performance (and even storage). I would not use a composite primary key because it has implications in other indexes you create. You should definitely spend some time researching that. When I need to maintain a business constraint like you have here, I create a unique index on the columns needed.
Hope you have a great day!

Hi Tom

Thank you for the reply !
I am going to look deeper into indexes now. But based on a quick search it seems that the choice between compound keys and unique indexes depends on a developer's personal preferences. If this is true then perhaps there will never be a single correct answer to my question.
Rephrasing my question a bit, would you say my design is proper if I eventually do decide to stick to compound keys ?

Best Regards 🙂

andycadley SSCertifiable Points: 5296 More actions · Answer 3

Get used to "it depends", such is the world of database development. Compound keys can be a good solution, but make sure they don't get too wide - sometimes it really is just better to use an "unnatural" value like an ID field and deal with that than creating uber wide compound keys that cause problems later of when you try to index data.