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

addressdatabase-design

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)

Best Answer

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)
Related Question