How do others know what is delivery address and invoice address if they are in the same table?

Question

When I searched around, the recommended way is to use the invoice address and delivery address in the same table and use a field "type" to show what it is. For example

Addresses(Company, address, city, type)

Type = 1: The address is a delivery address

Type = 2: The address is a invoice address

Type = 3: The address both delivery address and invoce address (same address)

If someone does not look at my code that uses the database, they can not know what Type 2 is. Is it delivery, invoice or both? Do you usually clarify this and how? For example, if someone wants to search the database (outside my code that calls the database) for all billing addresses, they do not know if they should use type 1, 2 or 3 to get all invoice addresses from a database client.

Compared to if you had two tables, then the name of the table says what it is and whoever can easily pick out all the invoice addresses.

Delivery(Company, address, city)
Invoice(Company, address, city)

Answer 1

An address is an address. There’s no need to store the same address details twice because it’s used for a delivery address and also invoicing. In your orders table you would have columns like delivery_address_id and invoice_address_id, they could have the same value in them. If you wanted to list all addresses that were used as a delivery address you can do a simple semi-join

Select * 
from address 
where address_id in (select delivery_address_id from orders)

Answer 2

We had a similar problem with regards to addresses. Initially the address details were stored in different tables. This led to lots of issues later on and address formats become very inconsistent over time so I would discourage storing addresses in multiple tables.

A neater solution that you could implement (which is what we are using and it's working well) is creating an Address table that just stores addresses (without the type). You then create an AddressType table which stores the different types of addresses. Finally, you have a linking table which stores (a) the ID of the Address record, (b) the ID of the AddressType and (c) the ID of the entity (e.g. Client) that the address belongs to.

The database might look something like this then:

This will provide you the following benefits:

• No duplicate address records when an address is both a delivery and invoice address

• Easily add more address types if needed in the future (e.g. business, postal, residential etc)

• Easy to query all the address belonging to an entity and can also filter by address type easily

  For example

  SELECT cal.ClientId, aty.Type, a.Unit, a.Building, a.Complex, a.Street, a.Suburb, a.City, a.Postcode, a.Country
  FROM ClientAddressLink cal
       INNER JOIN Address a ON a.AddressId = cal.AddressId
       INNER JOIN AddressType aty ON aty.AddressTypeId = cal.AddressTypeId
  WHERE cal.ClientId = 1
       -- If you want to filter by type
       AND aty.Type = 'Delivery'; 
  

Answer 3

Another option: Instead of 1, 2 and 3 use strings 'invoice' and 'delivery'. MySQL even has a column type enum for this purpose. Much more friendly to the developer and there is no significant overhead.

Answer 4

While Andrew's answer has a fair point about Table normalization and one field one purpose principal some database designs do make more sense to denormalize and use a field to specify multiple types. Sometimes for performance reasons, or business logic / application requirements, the reasons are very diverse and case by case, so I won't go into specific details of an example for the context of this question.

But to answer your question, in a case where you have a denormalized address and type field in your schema, usually the types can be understood from a logical perspective by analyzing the Entity Relationship Diagram (ERD) and/or physically through representation in the consuming application via an object implementation like an enum. Of course this adds extra maintenance in the consuming application to stay in sync with the database, and is one of the tradeoffs for not normalizing the field as Andrew's answer recommends.

Answer 5

would have used two boolean fields "delivery" and "invoice", makes it logical and easy to query. also you can keep entries for later use that are neither type right now. putting this in one integer field just asks for trouble later as you realized, good instincts!