0

I am currently modelling a database schema. In it, addresses are used several times, which follow a fixed structure. In our previous systems, the address relation was modelled in such a way that the address contained foreign keys, e.g. those of a user.

Reasoning: In this way, an address can only be attached to one parent. From my point of view, however, this is an absolute chaos because sometimes up to eight foreign keys exist in the address relation, whereby care must be taken that only one is filled.

Wouldn't it make much more sense to place the key on the parent, so that by means of a not-zero constraint it can be ensured that each parent has an address and the address relation is clean? The parent is also rarely/never navigated to from the address side.

Or am I violating any conventions by doing this? In such a case, doesn't cleanliness weigh more heavily than a "convention"? If desired, one could also model the previous requirements additionally using triggers?

Current approach: Constraint to ensure only one FK is set on address.

My approach: address_id as FK on parents.

4
  • 1
    Could you please add an example schema for each of the ways you are describing it could be structured? (Sorry I'm having a hard time envisioning it from your description.)
    – J.D.
    Feb 3, 2021 at 22:42
  • 1
    @J.D. I'm sorry. Added =)
    – Sven M.
    Feb 3, 2021 at 22:48
  • 1
    Thanks, much better! (I'm a visual guy. :)
    – J.D.
    Feb 3, 2021 at 22:50
  • Every relation has at least one CK, and a FK says subrow values somewhere appear elsewhere as a CK. (In SQL PK & FK mean different things than in the relational model.) Follow an information modeling & DB design textbook. This post asks us to rewrite one.
    – philipxy
    Feb 4, 2021 at 23:01

3 Answers 3

4

Yes so your approach / the second approach is generally more favorable and normalized because it leads to less redundant addresses. But you lose the flexibility of a user having multiple addresses this way, because of this one-to-one approach. If that's not a requirement of the logical schema, then your approach makes more sense.

Alternatively to support the normalization and flexibility best of both worlds, you can add one more table called userAddress that is a bridge / linking table between the two, supporting a many-to-many relationship by storing the user_id and address_id fields as pairs of all addresses for each user.

So in short:

  1. Approach 1: More flexibility, but less normalization / more data redundancy is possible
  2. Approach 2: More normalization / less data redundancy, less flexibility
  3. Approach 3 (Bridge Table): Flexibility and normalization so reduced data redundancy

Also to your second question regarding Triggers, they aren't all implemented exactly the same way across all modern RDBMS (because the ANSI-compliant spec for them is very minimal relative to the other features of SQL). So for a generalized answer (since we're not talking about a specific database system in the context of this question), it depends on the database system if you'd be able to replicate the desired behavior via a Trigger. (I see you personally use PostgreSQL, so I believe in that context, then it would be possible with a Trigger.)

1

Can a user or a company have multiple addresses? Normally, that's a pretty reasonable thing. Users have physical and mailing addresses. Companies have billing addresses and shipping addresses. If you put an address_id in the user table or the company table, you'd be limiting users and companies to a single address which is generally not what you want.

Can a user and a company have the same address such that changing one automatically changes the other? If both user and company have a row with the same address_id and the user goes in and causes the address row to get updated, do you really want to cause the company's address to change as well? It is possible that makes sense in your application but I'd think that normally you'd want to ensure that different types of entities reference different addresses.

As a rule, I'd generally have a mapping table between user and address that allowed a 1 to many relationship and an address_type table that let me specify what type of address it was. That way I'd have flexibility to define types of, say, a user's mailing address or a company's billing address or a combined user/company address. You can modify the data model to enforce rules like "a user can only be associated with certain address types" or "a user and a company can't reference the same address_id" but it's normally reasonable for those sorts of things to be business rules the application manages.

create table user (
  user_id integer primary key,
  user_name ...
);

create table address_type (
  address_type_id integer primary key,
  address_type varchar(20)
);

create table address (
  address_id integer primary key,
  address_type_id integer references address_type( address_type_id ),
  street ...
);

create table user_address (
  user_id integer references user( user_id ),
  address_id integer references address( address_id )
);
-1

1 st approach

create table address ( address_id integer primary key, -- fields related to address );

create table company( company_id integer primary key, -- fields related to company )

create table User ( user_id int primary key, address_id int foreign key references Address(address_id) company_id int foreign key references Company(company_id)

)

2nd if you have multiple address types you can map address types in to a mapping table and have the address_type_key in each table as a foreign key.

create table address_type ( address_type_id integer primary key, address_type varchar(100)

-- fields related to address type );

create table address ( address_id integer primary key, address_type_id int foreign key references address_type(address_type_id), address_line_1 varchar(100), address_line_2 varchar(100), State varchar(50), country varchar(50), postalCode varchar(10) -- fields related to address );

create table company( company_id integer primary key, address_type_key int foreign key references Address_type (address_type_id), address_key int foreign key References address(address_id) -- fields related to company )

create table User ( user_id int primary key, address_type_key int foreign key references Address_type (address_type_id), address_key int foreign key references Address(address_id) company_id int foreign key references Company(company_id)

)

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.