CODEWITHSUNDEEP
sqlpostgresql
Mike Varela
Mike Varela

Reputation: 527

SQL Model Structure - Person and Attributes tables

I’m developing an SQL database and want to separate out a few attributes of a Person table. These attributes will likely be updated or added to using CRUD operations themselves.

I’m having a hard time with something that might be simple and so I’m looking for some advice.

My Person model has basic stuff like name, email, phone etc. but also

And others like this these that would be best expressed an a select drop-down list in a form.

Currently I have fields in the Person table like

gender_id sexual_orientation_id etc…

Where this field is a relation to the ID field of those tables.

This is a

One Gender to Many Persons relationship

Wondering if this is a coherent way to structure the data. I’d like to keep these options in the database instead of a simple text array on the client for the form

The things that’s been hard for me is the amount of FK fields in the Person table. It’s seems off. I have like 10 of them for these select option sets.

Looking for some advice here. Thanks!!

Upvotes: 0

Views: 212

Answers (1)

Raiyug
Raiyug

Reputation: 21

Is it necessary to keep identity value of all those attributes in Person Table.

Like why do we need to have gender id in person where simply we can insert/update value from gender table at run time. It's not like in future someone is going to come and ask you to update couple of hundreds/thousand rows on basis of gender id , similar pattern can be seen for religion id , person type etc.

So in my opinion for values which are kind of universally unique such as gender, Religion, Sexual orientation you can keep value in person rather than foreign key relation.

if it can't be follow . so having 10 Foreign key in a table not a bad thing because here as per looking your requirement base tables have few rows, 10 foreign key will not be problematic even your table grow up to 100 GB .

Upvotes: 2

Related Questions