Database Design with Performance Issues and Use of Views

databasedatabase-designrelational-database

I am designing database for a school system. I am having to create a lot of tables and a lot of link tables to maintain a structure in the system.

I am trying to maintain foreign integrity and trying to create unique data entry as possible. But this will create problem with queries when I start building out the system. I will have to make a lot of joins and I am afraid that this take a toll on optimization and performance.

In this instance,

I have a student who is taking a course for a subject. While he takes the course he can be borrow a book related to that subject from library.

My tables would be

a. Subjects with: id, name

b. course with: id, name, subject id

c. link table to know that the student is taking the course: id, user id, course id

d. library item table id name

e. a link table to know which library item the student is borrowing: library id, id from c

I am trying to user primary key from c because this will make sure that if the user is no longer taking the course he would not be allowed to borrow a book. The link table will cascade if the relation does not exist between the course and the user.

The problem with this is that. If i need to know the name of the subject that the student has borrowed book for. I have to join four tables. I can simply just add subject id id e to get the subject name. But that is what I hate. If this is really an issue with performance would using views make the queries faster and is it really feasible to implement views in a project.

So I guess my questions in this are,

  1. Is it really a big performance issue to make multiple tables with unique columns. I have heard indexing columns help quite a lot?

  2. Are views helpful in maintaining the relational database and making the queries faster?

  3. And in table c I have not used separate primary key id because I want to make sure that the one student can borrow one item at a time. Is this a good practice?

Best Answer

Is it really a big performance issue to make multiple tables with unique columns. I have heard indexing columns help quite a lot?

This depends on the DB system. Almost any relational DB system I know of automatically will add an index for primary keys. Some DB systems also add indexes automatically for each FK constraint, some others do not. For foreign key columns to be used in JOINS, it makes a lot of sense to add an index beforehand, and you should check the manual of your DB how it behaves when creating new FK constraints.

Actually, performance depends also on how your select statements look like, a classic "JOIN" should make most DBs use the index, but adding some complex logic to your select statements can break the DBs ability to use the index. For your school system, I would not waste too many thoughts into this as long as you don't suffer from any real performance problems.

Are views helpful in maintaining the relational database and making the queries faster?

Also, this depends on the DB system. Some of them have mechanism for caching or indexing views, some have not. See this SO post for detailed discussion about, for example, MS SQL server. If you really want to know it, try it out and measure. In your situation, I would not introduce any views for solving any "hypothetical" performance problems. I would only introduce views if it helps making some of your queries simpler.

And in table c I have not used separate primary key id because I want to make sure that the one student can borrow one item at a time. Is this a good practice?

To make sure that the one student can borrow one item at a time, you should use a unique contraint on the combined IDs for students and items. This has not much to do with adding a separate primary key (which can be done, or not, there are good reasons for and against doing so, but in your case, I don't expect it to make a big difference).

Related Topic