4

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)
Xtreme
  • 141
  • 4
  • 4
    Just an somewhat related comment, while it is usual to have a master record like „customer“ or „order“ simply have multiple foreign reference to the „postal address“ table (as suggested in answers) keep in mind that finished/archived documents/records typically don’t want updates to be propagated. So either never Update address records but generate new address entry with IDs or store the business documents denormalized (or indepemdemtly versioned) instead. – eckes Feb 01 '21 at 02:35
  • I see that you are relatively active on Stack Overflow. Why not ask this there? It seems like a coding question to me. – Mawg says reinstate Monica Feb 02 '21 at 09:35
  • 1
    This sounds like an instance of a more general problem, namely, whether to store an enumeration as an integer or a string in the database. Both have their advantages and disadvantages, and some discussion can be found, for example, here: https://stackoverflow.com/q/1612319/87698. I use ints for "data tables" (which are usually only accessed by the application) and strings for "log tables" (which are often read by admins directly in the DB in case something went wrong), but that's just my personal preference. – Heinzi Feb 02 '21 at 09:50

5 Answers5

26

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)
Andrew Sayer
  • 1,732
  • 1
  • 3
  • 8
12

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:

enter image description here

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';
Ivan Kahl
  • 221
  • 1
  • 5
5

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.

Vilx-
  • 654
  • 1
  • 5
  • 11
3

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.

J.D.
  • 22,196
  • 3
  • 34
  • 78
-1

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!

Richard
  • 7
  • 1
  • 1
    I would say two boolean fields may run ya some trouble too. What happens when both fields are true for the same record? Following **one field one purpose** principal like in Adam's answer guarantees determinsitic meaning in each combination of values. – J.D. Feb 01 '21 at 05:50
  • @J.D. "Both fields true" is the same as `type = 3` – Barmar Feb 01 '21 at 16:35
  • @Richard Sorry that's true, a better question would be how to handle two different addresses for the same record? Probably would need to duplicate the record in that case then. – J.D. Feb 01 '21 at 16:54
  • 1
    The trouble with this idea is that there are likely to be additional types of address in the future. If `type` is an `INT` pointing to a lookup table, you just add a record to that table. If types are recorded with separate bit fields, you have to make a schema change, a much bigger deal. – Jon of All Trades Feb 02 '21 at 17:42