CODEWITHSUNDEEP
database-design
user1534664
user1534664

Reputation: 3418

How do you know what relationship to use?

I understand that there could be a relationship between the entity's, let's say, Customer and Order, but I don't understand to which table the 1, m or n belongs to when I create my own ERD... Are there any rules to understand what relationship I should use and in what order I should put the symbols (so is it 1:n or n:1)?.

In the following image I know each order has 1 customer since order_id is the primary key of the Order table. Is this the reason why there's a 1:n relationship and not a n:1? Or is it because the same customer can place an order multiple times, so the customer will be saved in the order table multiple times?

rdb

Another example:

Same question here. Why is there a 1:n relationship between t_course and t_course_taken? and why is it a 1:n relationship between t_student en t_course_taken?

relationship

Upvotes: 0

Views: 1624

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

There's already an accepted answer to this question, but I wanted to draw attention to one aspect of this question for the benefit of future visitors. It's the difference between analyzing the subject matter and designing the database.

Entities and the relationships among entities are really features of the subject matter, not features of the database as such. One discovers these features by analyzing the subject matter to find out how things work in the real world. It's important to understand that these features are discovered, not invented. In this sense, the relationship between courses and students is many-to-many simply because that is how it works in the real world (in every school I've ever looked at).

Once the real world is (somewhat) understood, one is ready to begin database design. Database design is a process of invention, but the invention had better be guided by the results of the discovery done earlier.

The initial design of the database involves tables, keys, and foreign keys. This is where normalization comes into play, and I second jbutler's recommendation to look into it. Note also that the analysis revealed two entities with a many-to-many relationship between them, while the design results in three tables, with a couple of one-to-many relationships between the outer two (entity) tables and the middle (relationship) table. This is how many-to-many relationships are modeled in the relational model.

This distinction between analysis and design is often overlooked by people just coming up to speed on database work. It's possible to go wrong here. An over emphasis on analysis can result in "analysis paralysis". An under emphasis can result in "ready, fire, aim".

Upvotes: 3

jbutler483
jbutler483

Reputation: 24559

Moving this to an answer:

Last comment answer: In short, yes.

However, in the first example that wouldn't make sense. One customer may have many orders, but a single order cannot belong to many customers.

In your second example,

A single student may enrol on many courses, and a single course may have many students. (Hence a many to many relationship). In order to combat this, We include a child table. Call it studentsCourse for simplicity.

A student can have many student courses, but a student course will now be specific to them.

A course may have many studentCourses, but again, a single studentCourse will belong to a single Course.

Making any more sense now?

BTW, I think you could make use of reading up on Database Normalisation (http://holowczak.com/database-normalization/) which will hopefully explain better how to break down tables (your textbook may even contain a chapter or two on the subject!)

Upvotes: 1

Related Questions