I am creating a persons schema where the the person table must be very simple because it will be father of other tables that completes it, but a person can be fisical or legal, so I wont put any identification numbers right in this table; for that there will be tables like person_documents, person_addresses, person_contacts etc.

The problem is that how to make this simple table works without unique numbers (like social security) that avoids the duplicate of a person.

So I think that I should create an Unique Index that holds for example both name and birth-date columns.

Should I have more columns to get 100% secure? After all there could be more than one person with the same name and same birth date right?

Maybe I could add a birth-place column, but then how would it impact on company person records...

create table person_base (
    id           bigserial,
    name         varchar(99),
    birthdate    date,
    _type        enum('human', 'company'),
    -- maybe birth_place    int references places(id)
  • 2
    Surrogate primary key will be much better choice in this case. Depends on RMDS you're using it can be implemented as auto-increment, or with sequences, or with UUID. – a1ex07 Jun 21 '16 at 14:59
  • Why do you need anything more than the id and the _type columns in the parent ("father") table? – ypercubeᵀᴹ Jun 21 '16 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.