Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.
share|improve this question
    
I am not sure how the question title relates to the question text? –  Emmad Kareem Feb 6 '12 at 21:23
    
I refactored my question a couple times before posting it. I didn't update the subject during that process. –  Ominus Feb 6 '12 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 Feb 6 '12 at 22:18

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Feb 6 '12 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.