How To Model a One-To-One Relationship in a Database (And Why)

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
In this video, you'll learn:
What a one-to-one relationship is in database design
Why you might use it
How to model one in a database design/ERD
A couple of guidelines on when to consider the one-to-one relationship

[@real8101]

I LOVE YOU!!! This is freak amazing! I will take your database course when you create one

[@DatabaseStar]

Glad you like the videos! Check out my website, I have many database courses available there.

[@pedrovirtuozo2596]

you are a life saviour! doing my final project for my course and needed this type of videos to refresh my mind :-)

[@DatabaseStar]

Awesome! Glad the video was helpful!

[@leandrociric5007]

Never saw anyone explaining these things like you. You're amazing!! Thank you so much.

[@DatabaseStar]

Thanks, I'm glad you liked it!

[@aadamkhan5217]

This helped with my revision and I am finally starting to understand databases MashAllah. Thank you 😊

[@DatabaseStar]

Glad it helped!

[@imranjalali]

great Explaination, keep doing the good work!

[@DatabaseStar]

Thanks! Glad you like it.

[@corndoggydogdog]

Really fantastic, thanks for this clear and concise video!

[@DatabaseStar]

Glad it was helpful!

[@smartygamer2512]

So useful video, thanks!

[@DatabaseStar]

Thanks!

[@armengraham6925]

List of the reasons for implementing a One-to-one relastionship was helpful for me.

[@DatabaseStar]

Thanks, glad you like it!

[@esamcoding]

@@DatabaseStar There is one more reason to split tables. security: if you have an external system that read data from your system ,you can give that database user a read-only access to the second table.

[@user-ms6gr8ff7s]

It was really helpful. Thanks a lot.

[@DatabaseStar]

You’re welcome!

[@alimihakeem841]

It's really helpful. Thanks

[@DatabaseStar]

Glad it was helpful!

[@stellamanuel5157]

Amazing 👏 explaining!!!!!

[@DatabaseStar]

Glad it was helpful!

[@PyroFire-Firework_is_a_passion]

Why not having a foreign key "employee_id" in the "emp_health_insurance" table, to eliminate the empty "health_insurance_id" columns?

[@DatabaseStar]

Yes, that would be another way to do it (and probably a better way!)

[@t3ntube357]

Yes, I think we should decide ourselves where to put the foreign key, which in this case as you said will be considered better, as "emp_health_insurance" table is not updated much like "employee" table

[@information88info]

Perfect thank you

[@DatabaseStar]

You're welcome!

[@sigmatau8231]

Now we r talking..thank you.

[@DatabaseStar]

Glad you liked it!

[@JustDoIt12131]

If the foreign key has not a unique constraint, what stops two employees from having the same health_insurance_id? It looks like a one-to-many relationship to me this way, to be honest.

[@DatabaseStar]

Yeah, if there is no unique constraints, it could be a one to many. If you want to enforce the one to one, you would probably need the unique constraint.

[@JustDoIt12131]

@@DatabaseStar Thank you for your answer.

[@user-iq6cp1jg7s]

Thanks dear

[@DatabaseStar]

You’re welcome!

[@yeet3499]

Since all the instances on the person table don't always have a relationship with the instances in the health table, but all the instances in the health table always have a relationship with an instance in the person table, isn't it a zero to one relationship ? Should we use the circle on the relationship then ? Thank you !

[@DatabaseStar]

Yeah, it could be considered a zero to one relationship as one of the rows is optional. The design is the same between the tables. However, one side can have one, so perhaps it's "zero or one to one".

[@Nicosfrs]

Thank you, amazing explaining, just have one question. 5:04 what would prevent workers having the same (id of) health_ensurence? I mean, if is not a preventing way, wouldn't this be exactly as the one to many relationship? because one worker can have one ensurance, and one ensure can belong to many workers? What I am missing here? thank you!

[@DatabaseStar]

Good question! WIth the design, nothing would stop two workers from having the same health_insurance_id. However, you could add a Unique Constraint to the employee.health_insurance_id column to ensure the value is unique.

[@Nicosfrs]

@@DatabaseStar Thanks! I understand now, you're the best!

[@sakthim7160]

I think health insurance table should have the foreign key pointing to the employee I'd? Is it right? If not what is the reason?

[@DatabaseStar]

You could do it that way instead I think. The relationship would need to only go one way

[@thanzeeljalaldeen]

this makes lot of sense. but i have a problem. imagine sometimes later the an employee changed the health insurance provider. in that case we will have a different health insurance id in the employee table. then that employee has now 2 insurance providers. will it be a 1 to many in this case?

[@DatabaseStar]

Good question. If they are changing from one to the other then it would just be updating the health insurance id in the employee table as you said. The old insurance id is no longer linked. If they have 2 providers at the same time then yes it would be a one to many.

[@thanzeeljalaldeen]

@@DatabaseStar , thanks. understood sir!!

[@wilsonho4577]

Hi, if i have 4 optional columns: pending date Pending reason Routed date routed reason Do you suggest i break it down in another table?

[@DatabaseStar]

Hey, without knowing the rest of the table design, I think you could leave the columns in the same table. It’s ok for columns to be optional

[@jlshrestha2541]

Hi Sir, which ERD program u used, nice ERD program. Could u pls tell d name of that pls?

[@DatabaseStar]

Sure, it's in the description, it's called LucidChart.

[@dxwi]

Take a shot every time he says "data".

[@DatabaseStar]

Hahah that's a lot of shots!

[@samiulhaq45]

Sir, which application do you use for ER-Diagram.

[@DatabaseStar]

I use LucidChart for my diagrams.

[@glowlog]

Why are Aussies so good at anything to do with databases?

[@DatabaseStar]

Thanks! I'm not sure, actually! I like seeing other Australians on KZbin or running websites though.

[@TheKennyWorld]

But how to implement a real 1:1 (not 1:0..1)? How is it possible to insert a record and respect referential integrity?

[@DatabaseStar]

Good question. You could add constraints on the related table to ensure the foreign key is populated. You can do this by adding a Not Null constraint on the column. You could also add a Unique Constraint on the foreign key to ensure that it is unique and only one value is related to the other table.

[@PriyankaDas-id4jy]

which ETL tool is this?

[@DatabaseStar]

The tool is called LucidChart and it's a diagramming tool.

[@ringtonesbyguessapps7737]

Database Administrator of course have the highest salary :p

[@emanueltejadacoste2250]

Whats that diagram software?

[@DatabaseStar]

It's called LucidChart

[@yehannk448]

Sir can you tell the realationship here,'one customers can buy many foods, but one food can be bought by one customer only'

[@DatabaseStar]

That sounds like a one-to-many relationship. The primary key of the customer would go in the foods table as a foreign key, so that each food can have only one customer ID. I think that meets your requirements?

[@dixztube]

Need to pay your sr dev more

[@DatabaseStar]

Haha good point!

[@dixztube]

@@DatabaseStar in all seriousness great videos and we all appreciate it.

[@theobalcells8030]

the sound is not good I am quite disappointed

[@DatabaseStar]

Thanks for the feedback. My sound has improved a lot in recent videos since this one was recorded a couple of years ago.