3

I have a table

user
  id (PK)
  email 
  username 
  password 
  status
  user_role
  profile_image
  created_at

and another table:

healthdata
  id (PK)
  age
  gender
  height 
  goal
  weight
  g_weight
  BMR 
  total_calories 
  a_level 
  date

The relationship between these two tables is one-to-one 'each user should have one health data'

I do not know if it's right to use the primary key (id) in healthdata table as a foreign key that references id in user table so that healthdata.id becomes a foreign and a primary key at the same time.

0

2 Answers 2

2

Can they? Yes. It's entirely possible to create the 1:0-1 relationship as you've demonstrated1.

But if you need that to be a 1-1 relationship, that breaks the relational model because the primary key of the child cannot be NULL, yet it is entirely possible to have a transaction that only inserts into the parent table. So, you must use stored procedures for inserts/updates/delete so everything is handled as if it were a single logical entity and changes are made within a single transaction.

Should they? Depends. This is a physical (not logical) consideration and the only reasons you should do this are:

  1. Performance - if the total size of the entity (in bytes) is large and a good portion of that data is read infrequently, it can improve performance to have the less-frequently accessed data in its own table to reduce the I/O for transactions that don't need to read that data.

  2. Backups- if you need to have a different policy for certain data elements (or need to exclude them from backups entirely), you have some options if the data is stored in a separate table.

If there are security concerns around access, you could partition your data into separate tables and assign permissions that way, but you'd be able to achieve the same thing with views/stored procedures and denying direct access to the table.


1 I would question if HealthData wasn't actually a series of measurements with the primary key being (UserId,Date) or something similar.

6
  • To get around this, you must use stored procedures for inserts/updates/delete so everything is handled as if it were a single logical entity. Now enough. Fake row in at least one table must exist, and the queries will be: insert into t2 set id=@_fake; insert into t1 set id=@_new; update t1 join t2 set t2.id=t1.id where t2.id=@_fake;
    – Akina
    Nov 11, 2022 at 17:54
  • @Akina - if you allow inserts to the parent table and child table separately, all columns of the child are effectively NULLable. If this is in conflict with the model, you need the procedure to ensure the insert to the child table is always attempted and the modifications succeed or fail together as it would in a single table. Unless I'm misinterpreting your comment.
    – user212533
    Nov 11, 2022 at 18:01
  • all columns of the child are effectively NULLable. This column is primary key also. So it cannot be nullable. We have clear 1:1, PK-to-PK.
    – Akina
    Nov 11, 2022 at 18:06
  • Yes, which is why if you're going to partition the data across two tables then you'd need the procedures to ensure all modifications either succeed or are rolled back. Otherwise you're stuck with violations to the model.
    – user212533
    Nov 11, 2022 at 18:15
  • 1
    @ypercubeᵀᴹ There are DBMSs where (FK) constraints can be deferred. Of course. But OP uses MySQL.
    – Akina
    Nov 11, 2022 at 19:21
-1

Yes, but at most one can be AUTO_INCREMENT, else they would be fighting over how to number them.

No, in the sense that is rarely wise to have two tables instead of one. There are exceptions; what is your reason for wanting two? Perhaps...

  • You want to give access to the health data to fewer user logins?
  • Or the health data is optional and not available for all people. This would lead to fewer rows in one vs the other, and LEFT JOIN to get NULLs when you do Join them.

It seems quite dangerous to have both FOREIGN KEYs do any CASCADEing.

FOREIGN KEYs provide certain integrity checks, but they are not mandatory when building a schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.