Menu
One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table?
357
One-to-Many is the most commonly used relationship among tables. A single record from one table can be linked to zero or more rows in another table. Let's take an example of the Employee and Address table in the HR database. The Employee table stores employee records where EmployeeID is the primary key.
MySQL/MariaDB does not contain any special options to define the one-to-one relationship, but you can obtain one-to-one doing this: in the second table add a foreign key to point to the primary key from the first table and as constraints add it as unique.
In a relational database, each table is connected to another table using the Primary-Foreign Key constraints. In One-to-One relationship, one record of the first table will be linked to zero or one record of another table.
Since Primary Keys are UNIQUE by default, this makes this relation One to One. To join tables I can use this: What is the best way to create One to One relation in MySQL? Are there any other solutions other than these two?
Number and type of columns. There is a limit on the size of the columns in a table. See here. There is a maximum of 8,060 bytes per row.
Very large tables can also affect performance and can be difficult to optimize and index well.
This is apart from keeping data the is conceptually different, apart from each other. For example, a country and currency have a 1 to 1 relationship (illustrative example, I know this is not always the case). I would still not keep them together.
50
You'll find some information about when it's useful to create one-to-one relations under http://onlamp.com/pub/a/onlamp/2001/03/20/aboutSQL.html
The most important thing is following:
The key indicator of a possible need for a one-to-one relationship is a table that contains fields that are only used for a certain subset of the records in that table.
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
