Database design with performance issue and use of views

Question

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?

Answer 1

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

Answer 2

DB systems are fast. Unless you are doing something like web portal for many schools to use your software on the same platform, you should usually have no trouble with a regular HW (provided that you generate the queries in a somewhat sane manner).

That said, if you still have doubts it is not that difficult to create an script that will populate your database with hundreds or thousands of professors, dozens of professors and matters, etc. There you may test your platform and decide if you really need to spend time "tuning" the model (my bet is you will not need to).

In general, it is recommended to avoid doing premature optimization. You may end using days and weeks of efforts in updating something that was ok to begin with. If you design well your system (v.g., an programming layer that abstracts the access to data), if you find later in the development that you have performance issues, you can take on them without having to modify much of your system.

Also, regarding

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

The "one student can borrow one item" is a business rule. At such, at any time the principal may come to you and say "hey, now we let them borrow two items". I would prepare the model to allow N borrowings and enforce the "one item only" in code (maybe even with a separate class to check the restrictions, allows you to swap the class quickly when needed).

Answer 3

I hacked a basic example with Mysql Workbench in 5 minutes

You can have different methods to keep track of borrowed and returned, I showed an example with borrow_history to keep history and you can manage if library_items are out or not via "borrowed_until" field. You can also track how many books a student took out via "borrowed", that you can += 1 upon borrowing and -= 1 upon returning.

You can compare this with Wordpress Database Schema and see how much smaller your database schema is.

You don't have to worry too much about the schema until you hit few million rows, and then correct indices will take care of most of the stuff for you. If you get much larger, you may want to try some Nosql solutions in tandem.

Try to check some business logic that you mentioned in your application, doing it on the database can be more difficult and may require learning stored procedures, triggers, etc. I think it would benefit you to learn those after you master database schema design and indexing techniques.

You can worry about a lot of things, but "premature optimization is the root of all evil". Joins will not be a problem until you go really crazy with it, maybe up to 10 joins for a query may start causing problems. But if your database is small, it won't matter. Obviously you must index the foreign keys, but I am assuming you would know that. Only way to figure out these things is to create fake data and test how hey perform.

Answer 4

1 -- should not be a problem until you hit millions of rows. Generally speaking you should start out with a clean "logical" schema and change it when and if you hit a performance problem.

2 -- Views are just "canned sql" querying a view is no different from running the underlying SQL. There are good reasons to use views but performance is not one of them.

3 -- Up to you really, depends on your requirements. (But your example does show an id :-) )

Answer 5

I think it is a good idea to take query performance into consideration early in the design phase, but at some point, your database has to hold the data required by your application. Even if your table design isn't optimal, you can change it later (easier said than done), improve indexing, cache query results (different databases offer their own solutions), create denormalized tables that hold pre-calculated/joined report results, add more hardware.

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

At some point linking many tables will cause a performance problem; however I think it is more than 4. Indexing will help. At some point too many indexes will limit performance on data changes because all those indexes need to be maintained. Since this is for a school system and student's courses change with each term, you can archive past terms, so this database may not grow that much. No one is going to check out a book for a course they tool last year.

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

Not really. They're just an object in the database that holds all the text for your query. You need to design high-performance queries regardless of where they are stored. It can make reuse easier, but beware, having multiple levels of views (a view of a view of a view) will hurt performance in most RDBMS. Databases don't reuse code as well as other languages.

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?

You can still use a separate primary key along with another unique constraint. Since you're relying on this unique constraint to hand this requirement, if you have to include or exclude another column(s), you don't have to change the relationships with other tables or possibly have to rebuild them. Currently, you're not relying on this to join to another table.