'concept of domains and subdomains for small database entities

recently I learned a concept called by my teacher "Domains and subdomains" in which small tables are generalized in which less than 5000 data or rows will be saved, I show you an example:

Entities

enter image description here

level dates:

   id_level  description
      01      continent
      02      country
      03      state

domain dates

id_domain       detail    level    parent
      01       America      01       -
      02         USA        02       01
      03        Europe      01       -
      04        Africa      01       -
      05          UK        02       03
      06        Aberdeen    03       05

If this concept has another name, it would help me a lot to know it.

Then we had to make an abstraction of this and take it to common entities like the following example:

enter image description here

level dates:

    id_level description
       01     address
       02     phone
       03     gender

domain dates

id_domain detail         level parent
    01    male            03    -
    02    female          03    -
    03    mobile phone    02    -
    04    home address    01    -
    05    home phone      02    -
    06    work address    01    -

Person dates

id_person name phone_type phone_value address_type adress_value gender
    01    Jhon     03       39249927     06          a place...   01
    02    Mary     05      2489540300    04         a place2...   02

but i get an error saying, the duplicate key name when i try to make the relation

ALTER TABLE person
ADD CONSTRAINT FK_PERSON_DOMAIN
FOREIGN KEY (address_type)
REFERENCES domain(id_domain);

Error

Duplicate key name 'FK_PERSON_DOMAIN'

Something I should add is that I already did the nesting with phone_type, after this it does not let me nest the other two attributes as shown in the diagram

This is the relationship that worked perfectly:

ALTER TABLE person
ADD CONSTRAINT FK_PERSON_DOMAIN
FOREIGN KEY (phone_type)
REFERENCES domain(id_domain);
mysqlsqldatabasemariadb


Solution 1:[1]

I call those "lookup tables." They usually have characteristics of having a small number of rows, and also they don't change frequently. They may have an integer id primary key, or they may have a natural primary key.

The error you got about the duplicate key name is caused by the rule that constraint names must be unique across all tables in a schema. In other words, there must be only one constraint named FK_PERSON_DOMAIN_PERSON in the schema.

You said you already created a constraint with this name for the phone_type column. If you want another constraint for the address_type column, you must choose a different name for that constraint.

Here's an example of what I mean:

ALTER TABLE person
ADD CONSTRAINT FK_PERSON_PHONE
FOREIGN KEY (phone_type)
REFERENCES domain(id_domain);

ALTER TABLE person
ADD CONSTRAINT FK_PERSON_ADDRESS
FOREIGN KEY (address_type)
REFERENCES domain(id_domain);

See that the constraint names are different.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions