Pro's/Con's to database table layout as it relates to maintenance & extensibility

Question

Basic pseudo class layout below...

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

class CustomerLocation(Location):
    customer_id
    account_rep 

class VendorLocation(Location):
   vendor_id
   drop_ship_cut_off_time

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.

Answer 1

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

Edit

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.

Answer 2

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.