5

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.

I have 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.

db relationship

Main question: If I decide to stick to compound/composite keys, is my design proper or can it be improved further ?

Side question: what would be the best approach in my case, with the following considerations in mind:

  • expected records around 50000
  • questions table (tb_QnA) is going to be the most queried table (for selecting and for adding/deleting data)
  • the tables tb_Class, tb_Subjects and tb_SubjectClass are not going to change at all
  • ease of writing queries when connecting the database to a c# winform application (for data entry and generating reports)
  • the project is going to be desktop based

I have mainly been following the Database design tutorial on tekstenuitleg.net and video tutorials by WiseOwlTutorials. I have also recently started reading the book Relational Database Design Clearly Explained by Jan L. Harrington.

Improve this question
4
  • You may also want to re-consider your naming convention.
    – Michael Green
    Aug 30 '18 at 10:17
  • @MichaelGreen You mean names of tables ? Can these names cause problems later on ?
    – Paul
    Aug 30 '18 at 11:23
  • Yes I mean table names. Specifically the "tb_" prefix. SQL Server cares not one whit. Your future self, and anyone else using this DB, may, however. Follow the link in my original comment to see copious discussion of this topic in other Qs and As.
    – Michael Green
    Aug 31 '18 at 0:12
  • Ohk I get the point now. I used prefixes so that its easier to spot if im linking tables or queries to controls in MS Access forms. This particular project will be desktop based and I'll be the sole user/manager for its life so I guess I can leave it like this for the time being. :)
    – Paul
    Aug 31 '18 at 9:30
4

Does making foreign keys lead to data duplication / repetition or is it just a reference / pointer to data stored in the parent table ?

Foreign key restricts the possible values in one table's column accordingly to the presence of the same values in another table's column. Values are stored in the both tables so they are duplicated. To avoid (in fact - to reduce) duplications normalization should be used. That's means you have the reference table where all distinct values are stored and all other tables contains only IDs of that values.

Should compound keys be avoided wherever possible ?

No. Even more, compound keys are the only way to speed up the query with complex conditions that involve multiple columns. Compound keys can be avoided by the price of performance.

Improve this answer
4
  • Thank you for the answer. So if I understand correctly: - compound keys actually improve performance - when using normalisation, data duplication cannot be avoided but only reduced (because we can use integer IDs which use less space than strings) ?
    – Paul
    Aug 29 '18 at 9:37
  • 3
    @Paul Integer IDs compared in the single fast CPU-optimized operation. When you compare strings first you need to apply the charsets and collations to the both operands and then compare char by char until first difference will be met or one or both strings will be exhausted. Integers have significantly lower overhead. Some data types like TIMESTAMP or BIT internally are represented by some kind of INTs so they are fast as INT too.
    – Kondybas
    Aug 29 '18 at 10:53
  • @Paul Compound index performance do not related to the normalization. Normalization is an attribute of the data logical structure. Indexes are related to the physical structure of the data. Some database with no single index still remain the correct database but VERY slow. DB performance heavily depends on search. Indexes allows to replace brute-force linear search by some faster alternatives like BTree/RTree/RBTree/SkipLists/etc. That tricks do not impact the data, they are hidden on the underlaying layer of realization.
    – Kondybas
    Aug 29 '18 at 11:05
  • Replacing or augmenting repeated (subrow) values by other (somehow more id-ish) values is not normalization.
    – philipxy
    Mar 10 '20 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.