CODEWITHSUNDEEP
sqldatabasepostgresqlentity-relationship
JavaUser
JavaUser

Reputation: 26344

How to model this particular table relationship

I want to create data model for the below scenario. I have a table called PERSON and HEALTH_STANDARDS.

PERSON has id,name,address,email HEALTH_STANDARDS has weight,height etc.,

In UI , if the user select a person and it has to show the standard health attributes . Next, user will change the health attributes according to the selected person. Rest of the persons will have the standard health attributes.

How can I model this . Do I need to create one more table PERSON_HEALTH_ATTRIBUTES and do the mapping here.

Upvotes: 2

Views: 47

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

It seems to me that HEALTH_STANDARDS are constants/defaults that don't actually correspond to an actual person, therefore there is no direct relationship between PERSON and HEALTH_STANDARDS. Rather it can be implied when a given person has no row in PERSON_HEALTH_ATTRIBUTES i.e. it defaults to the standard value e.g. restricting the attributes to just weight for simplicity:

CREATE TABLE HEALTH_STANDARDS
( person_weight_kg INTEGER NOT NULL );

CREATE TABLE PERSON
( person_id INTEGER NOT NULL UNIQUE,
  name VARCHAR(35) NOT NULL );

CREATE TABLE PERSON_HEALTH_ATTRIBUTES 
( person_id INTEGER NOT NULL UNIQUE
     REFERENCES PERSON ( person_id ),
  person_weight_kg INTEGER NOT NULL );


SELECT person_id, person_weight_kg
  FROM PERSON_HEALTH_ATTRIBUTES
UNION
SELECT person_id, person_weight_kg
  FROM PERSON
       NATURAL JOIN HEALTH_STANDARDS
 WHERE person_id NOT IN ( SELECT person_id
                            FROM PERSON_HEALTH_ATTRIBUTES );

Upvotes: 1

Navnath
Navnath

Reputation: 133

I think you should design the table structure like bellow described in a image.

where a person has one to many relation with health standard as person can have many heath standards like height , weight , colour. so we should create one more table which will contain the person heath standards data here we have created person_health_standard table

health_standard is a master data

enter image description here

Upvotes: 0

wvdz
wvdz

Reputation: 16641

Because there is a one-to-one relation with PERSON and their HEALTH attributes, you should just combine them into 1 table. If there is only 1 default value for the default HEALTH_STANDARDS, I would just implement that as default values on the PERSON table.

Upvotes: 0

Related Questions