Is there a more efficient way to handle multi-valued attributes other than creating a relationship table?

Question

I have three tables, tbl_school, tbl_courses and tbl_branches.

Each course can be taught in one or more branches of a school.

tbl_school has got:

id
school_name
total_branches
...

tbl_courses:

id
school_id
course_title
....

tbl_branches:

id
school_id
city
area
address

When I want to list all the branches of a school, it is a pretty straight forward JOIN.

However, each course will be taught in one or more branches or all the branches of the school and I need to store this information. Since there is a one-to-many relationship between tbl_courses and tbl_branches, I will have to create a new relationship table that maps each course record to it's respective branches.

When my users want to filter a course by city or area, this relationship table will be used.

I would like to know if this is the right approach or is there something better for my problem?

I was planning to store a JSON of branches of courses which would eliminate the relationship table and query would be much easier to find the city or area pattern in JSON string.

I am new to design patterns so kindly bear with me.

@MikeSherrill'CatRecall' JOINS are expensive so would it be more efficient to skip the relationship table and store JSON data containing branches related to the course in the tbl_course itself? This would mean that we eliminated JOIN and a table for the rest of the application life. Or is there anything else that can do and that is more acceptable? — Zulfi Ansari, Apr 15 '15 at 12:57
SQL databases are optimized for joins. Measure, don't guess. — Mike Sherrill 'Cat Recall', Apr 15 '15 at 14:10
That answers my question. Thanks Neil and @MikeSherrill'CatRecall' — Zulfi Ansari, Apr 16 '15 at 06:07
You have accepted a simple answer, but there are errors in your file design, as well as in your approach to problems. If you are willing to change your acceptance and consider a full answer, which would be helpful especially since you are new, I can post it for you. — PerformanceDBA, Apr 19 '15 at 02:58
@PerformanceDBA You don't have to ask me before helping me and others. Kindly post what you have to say. — Zulfi Ansari, Apr 19 '15 at 06:06
@Mohammad Urfi. Sorry if I wasn't clear. I wasn't asking permission. I was asking if you would re-consider your choice of Answer. — PerformanceDBA, Apr 24 '15 at 02:06
@Mohammad Urfi. I have corrected the Pascal case, and added a couple of links. — PerformanceDBA, Apr 24 '15 at 11:04

score 1 · Answer 1 · edited May 23 '17 at 12:15

Issues

The table description you have given has a few errors, which need to be corrected first, after which my proposal will make more sense.

The use of a table prefix, especially tbl_, is incorrect. All the tables are tbl_s. If you do use a prefix, it is to group tables by Subject Area. Further, SQL allows a table qualifier when referring to any table in the code:
```
`... WHERE table_name.column_name = "something" ...
```
If you would like some advice re Naming Convention, please review this Answer.
Use singular, because the table name is supposed to refer to a row (relation), not to the content (we know it contains many rows). Then all the English used re the table_name makes sense. (Eg. refer my Predicates.)
You have some incorrect or extraneous columns. It is easier to give you a Data Model, than to explain each item. A couple of items do need explanation:
- school.total_branches is a duplicate, because that value can easily be derived (by COUNT() of the Branches). It breaks Normalisation rules, and introduces an Update Anomaly, which can get "out of synch".
- course.school_id is incorrect, given that each Branch may or may not teach a Course. That relation is 1 Course to many Branches, it should be in the new table you are contemplating.
- By JSON, if you mean construct an array on the client instead of keeping the relations in the database, then no, definitely not. Data and relationships to data, should be implemented in the database. For many reasons, the most important of which is Integrity. Following that, you may easily drag it into the client, and keep it there for stream-performance purposes.
  
  The table you are thinking about is an Associative Table, an ordinary Relational construct to relate ("map", "link") two parent tables, here Courses to Branches.
Data duplication is not prevented. Refer to the Keys is the Data Model.
- ID columns you have do not provide row uniqueness, which the Relational Model demands. If that is not clear to you please read this Answer.

Solution

Here is the model.

Proposed School Data Model

Please review and comment.

I need to ensure that you understand the notation in IDEF1X models, that unlike non-standard diagrams: every little notch, tick and line means something very specific. If not, please got to the IDEF1X Notation link at the bottom right of the model.
Please check the Predicates carefully, they (a) explain the model, and (b) are used to verify it. It is a feedback loop. They have two separate benefits.

If you would like more information on Predicates, why they are relevant, please go to this Answer and read the Predicate section.

If you wish to thoroughly understand Predicates, with a view to understanding Data Modelling, consider that Data Model (latest version is linked at the top of the Answer) against those Predicates. Ie. see if you understand a database that you have never seen before, via the model plus Predicates.
The Relational Keys I have given provide the row uniqueness that is required for Relational databases, duplicate data must be prevented. Note that ID columns are simply not needed. The Relational Keys provide:
- Data Integrity
- Relational access to data (notice the ease of, and unlimited, joins)
- Relational speed
None of which a Record Filing System (characterised by ID columns) has.
Column description:
- I have implemented two address_lines. Obviously, that should not include city because that is a separate column.
- I presume area means something like borough or county or the area that the school branch operates in. If it is a fixed geographic administrative region (my first two descriptors) then it requires a formal structure. If not (my third descriptor), ie. it is loose, or (eg) it spans counties, then a simple Lookup table is enough.
- If you use formal administrative regions, then city must move into that structure.

score 0 · Accepted Answer · answered Apr 15 '15 at 09:32

0

Your approach with an additional table seems the simplest and most straightforward to me. I would not mix JSON in this.

answered Apr 15 '15 at 09:32

Plamen G

4,729
4
33
44

Is there a more efficient way to handle multi-valued attributes other than creating a relationship table?

2 Answers2

Issues

Solution