Relation between tables

Hi guys !
i’m new to database world besides i have i small problem to make relation between tables however the problem is when i make a foreign key or create a new intermediate table ?
so can anyone help me
thanks

So the answer to this is dependent on the nature of the relationship.

There are three possible scenarios. In most references, you will generally find them listed as 1-1, 1-M, and M-M.

1-1: A One-to-One relation means that for any entry in the first table, it will be linked to exactly one entry in the second table.
Worth mentioning that if this is a strict relation, the tables could be merged. Most often, this is actually a 1-1* relation, in such that the entry in the second table may or may not exist (there are 0-1 entries in the second table, but no more than that.)
Hard to give an example of a strict 1-1, because they could be merged, but a 1-1* could be the relation between an employee and their health insurance. Not every employee will have health insurance information (perhaps it is private), but if they do have information, there will only be one set of that information, and that information only relates to one specific individual.

1-M: A One-to-Many relation means that for any entry in the first table, it can be linked to any number of entries in the second table. Sometimes you may think about this the other way around and say many entries in the first table are linked to a single entry in the first.
To reuse our employee example; there can be many employees that have the job title “Associate”, and that title has certain information that is global to all employees with that title. This is where a Foreign Key resides - as long as the title is a key in my titles table (and it should be.), I can form a foreign key between the two tables.

M-M: Many-to-Many relations get messy. Both sides of the relation can have many entries. This is where an intermediate, or join table, is used. Putting an intermediate table between the two tables breaks an M-M relationship down into two 1-M relationships.

Consider the case that I have an orders table that tells me who ordered things, and I have an items table that tells me information about the items I sell.

An order consists of many items; conversely, an item can be included in many orders. I need an additional table - let’s call it order_items because we’re clever with names - to break down this relationship into manageable chunks.

3 Likes

can you please give more details about the problem

2 Likes

I am not as experienced with databases as many are but it is my understanding that we need an intermediate table only for one-to-one relations. For one-to-many and many-to-many the “many” fields are not unique and the “one” fields are unique.

I think this is the most difficult part of database design. I think the main reason it is difficult is the terminology used by database people. (It took time for me to get straight in my head what they mean by primary key and foreign key) If what I said above is accurate then I hope it makes things simpler and easier to understand.

from the sql side i don’t have any problem
but when i want to study the case to make relation between tables
i can’t find the logic , for exampl when when i code with any programming language i can find my fault without the compiler did.
did u understand me ? @r937

sorry, no

What tool are you using to do that? And if you can provide details of the relation you are trying to create then that would help too.

I think what we really need is a practical example of the type of data you are dealing with and an explanation of the type of relations required for it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.