Basic pseudo class layout below...

class Location(): # Class containing information about a location.

class CustomerLocation(Location):

class VendorLocation(Location):

Let's say you have locations which are going to be used to store location info for customers and vendors.

What would be the pro's/cons of the three options in how they relate to application maintenance and extensibility...

  1. One large table containing all fields.
  2. One large table containing all common fields with related tables containing specialized information about the large table records.
  3. Many small tables containing all fields related to specific uses.CustomerLocation table VendorLocation without using a "master table" for common fields.
  • I am not sure how the question title relates to the question text?
    – NoChance
    Commented Feb 6, 2012 at 21:23
  • I refactored my question a couple times before posting it. I didn't update the subject during that process.
    – Ominus
    Commented Feb 6, 2012 at 21:30
  • This isn't "table layout". It's "Normalization". It's not a "maintenance" or "extensibility" issue. It's a semantic issue. The normalized tables have to be correct.
    – S.Lott
    Commented Feb 6, 2012 at 22:18

2 Answers 2


Good database design implies that you normalize your database adequately. You could generalize the location, assuming that you want to record several locations for a customer or vendor, you could do this:

  • Customer (0,1,more) Location

  • Vendor (0,1,more) Location

  • Location

I don't see why you have 2 separate classes CustomerLocation, VendorLocation.

You could go further in generalizing Vendor and Customer as Party with Vendor and Customer inheriting from the abstract type Party.

The above is valid unless:

  • A customer may be a vendor or vice verse and/or

  • The same location is the address for more than one vendor or customer


You can't use party with inheriance as I suggested above (before your comment) since your comment implies that customers can be vendors and vendors can be customer. So, in fact you only have a party with non-mutually execlusive party type that can be represented by 2 boolean columns: IsCustomer and IsVendor (this may be complicated by creating separate table to record when the status is acquired).

Now, the relationship between a party and location is many-to-many.

The intersection table resulting from the many-to-many relationship can have unused columns.

In designing a database structure don't complicate the design because there may be unused columns. Some ERP applications have 100s of columns in a table and only few are ever used! Just make sure you specify unused columns are Varchars and choose the null-ability wisely.

  • Customers can be vendors and vendors can be customers. Customers and vendors will have multiple locations. My example is pretty vague but consider that customer locations might some fields like "default_ship_method", 3rd_party account number that you wouldn't need for a vendor location. Also this kind problem/solution would be applied to more than just locations. For instance "comments" where you would have customer comments, hr user comments ... you get the idea.
    – Ominus
    Commented Feb 6, 2012 at 21:40

Go for option #2. There are no wasted columns just sitting there with null values. And there is no duplication of columns in multiple tables. You have exactly what you need. A lot of real systems use option #2.

It's used in a lot of accounting software. You'll have a master Transaction table. If your transaction is a "special" kind with extra fields it will have a record in a another table (1 to 1 relation) to store the extra fields.

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.