Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Question Unanswered: Database Design Help

    Hello,

    I have some question about database design. I have Company Seller and Company Buyer, both of them would have 10 same fields like address, name, logo, city..etc
    Also I have 'contract' table where we have like contract_id, buyer_id, seller_id
    and they can exchange messages so we would have table 'message'
    with message_id, seller_id, buyer_id.
    My question is since Company Buyer and Company Seller have let's say 10 columns with same name and 5 columns exist for Seller but not for Buyer,
    would it be best to create one table named company with fields company_id,
    company_type(Buyer, Seller) or it would better to have different company tables for buyers and sellers no metter they have same fields?

    I'm just not sure should I have one company table and one field to distinct Buyer and Seller or I should build different tables?
    Best Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use the same table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    If "seller" and "buyer" are fundamentally different entities (such as Vendor and Customer) then I'd keep them separate.

    They're sure to grow with different needs and I can't imagine ever needing the two datasets in the same query (ie: UNION). Putting them together sort of paints you into a corner.

    You could go with a shared Address table. That way from a programming perspective you could potentially have a single object for managing address.

  4. #4
    Join Date
    Sep 2010
    Posts
    4
    Thanks for your opinions, there is one more thing that makes me thinking to use 2 different tables, if we take them as Vendor and Customer(both types are actually companies) like vich said, I also
    need to have an option for Customer to create his list of Vendors and based on that list customer would purchase services. So I would need to have table like customer_to_vendor where I would need to have customer_to_vendor_id, customer_id,
    vendor_id (or maybe just combination customer_id, vendor_id as primary key).

    "You could go with a shared Address table." If I have shared address table
    should I have field like company_id, company_type so I could know where to check details or you think that I should have both id's like customer_id, vendor_id in address, so if address belong to vendor we would have vendor_id = 2 and customer_id = 0
    or to have id = 2 and customer_type = 'Vendor'? beside Address they both would have billing info, so I suppose that could be one more table built on same principles like address table.

    Also if someone could recommend some book about designing database it would be much appreciated.

    Best Regards and thank you

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have whats common in a common table, have whats different in another sub table(s),
    if a customer can also be a supplier then common table
    if a customer cannot be a supplier (or there won't be many) then a separate table for each may make sense.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    There are many ways to skin a cat.

    I like simplicity I can wrap my head around. In theory, a company is a company but in practice, there's little a vendor and customer have in common and much they don't. Those things that are in common are often different values depending on if its buying or selling.

    For example; they might be a huge outfit so you may extend them 60 days terms but they make you pre-pay. So you end up needing different "Payment TermsID" fields; ie: BuyingPaymentTermsID and SellToPaymentTermsID.

    You might even say "no problem, I'll make a different entry in the table for them as a Vendor vs. Customer, so they can have different address, terms, etc".

    Over time; I've found that the kinds of stuff you add to a Customer table to satisfy needs would just be clutter for a Vendor. For example; a customer has a SalesRepID that links to your SalesRep table. A vendor has a contact - or maybe even several contacts. If you now try that same relationship you wind up with Vendor Contacts (their ever-changing sales reps) polluting your internal SalesRep table. Now that sales rep table might need some commission and performance info ... stuff you don't care about for vendor contacts.

    Database fields are spawned for the purpose of satisfying real-world needs. For customers; that usually has a lot to do with buying behaviour. For vendors; it might be delivery and price performance. They're just different worlds and therefore, even though some basic fields (like address info) is the same and could be boxed up into its own table, the rest just isn't related. I like to think of the functionality and purpose of the objects; not just it's shape. This advice might conflict with a computer scientist's idea of 100% normalized data but for someone of my meager brain capacity, where I rely on relevant fields with relevant names, it's worked well.

    Like I said; better not to paint yourself into a corner - but at the same time it's good to recycle common stuff where you can (like address info).

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by solarisdev View Post
    I also need to have an option for Customer to create his list of Vendors and based on that list customer would purchase services. So I would need to have table like customer_to_vendor where I would need to have customer_to_vendor_id, customer_id,
    vendor_id (or maybe just combination customer_id, vendor_id as primary key).
    I don't comprehend what you mean by a "company's list of vendors". If a parent table (like Company, Customer, or Vendor) needs a list of anything (change history, whatever) you just create a table that has a ParentID field right?

    From a financial side of things; some Customer/Vendor connection may be desirable.

    For example; if your company sends them to Collections as a customer, you probably don't want to send them a check for a recent purchase.

    IMO, the best way of linking Table A and Table B on a 0-or1 to 0-or-1 relationship (that is, either table may be missing the linking entry) is to create an intermediary parent table. In this case "Company" table. Vendor and Customer tables would contain a CompanyID field non-nullable. Company entry would be required. It could also be made to contain things like default Name and Address info (to override Vendor/Customer values if null).

    That lets the programmers link together the finances, etc when it's appropriate. Using cross-linking fields would complicate the queries (if this then that etc). Better to have a dependable parent-to-1-or-2 children relationship.

    Sorry to be preachy - I just have trouble imagining this being common and if it did happen, the relationship is all but irrelevant for nearly all programming. Almost certainly; their AP and AR departments don't make this link and you'll have to maintain entirely separate accounts anyway. For example; if the overall balance is $5,000 but you purchased $7,000 in goods from them (and they got $2000 from you); they'd be pissed if you just sent them $5,000 and you'd be in trouble if you didn't bill them for the $2,000 for their purchase from you. However; the link will be there in case you ever have to.

    In other words; you *could* use cross linking fields as you suggested, since it's not a relationship worth the effort of creating a parent. Having a common parent is simpler to write queries against so if you forsee using this relationship often then that's a better route. It adds the advantage of sharing common info. On the other hand; cross-linking fields inherently enforces the 1-to-0-or-1 relationship (ie: you could never have 2 Vendor entries for the same "company").
    Last edited by vich; 05-25-12 at 15:37.

  8. #8
    Join Date
    Sep 2010
    Posts
    4
    Thanks for all of your help, I think I have all info I needed now. If you could just recommend some good site with tutorials or some book, that would be great.

    Cheers

  9. #9
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Since your initial question was really one of "Normailzation", here's a 30 minute overview that also includes some nice golden rules to design by.

    Note: Normalization rules are made to be broken so don't freak out that they seem constraining or overkill. That's covered under "DeNormalization".

  10. #10
    Join Date
    Sep 2010
    Posts
    4

    Thumbs up

    Quote Originally Posted by vich View Post
    Since your initial question was really one of "Normailzation", here's a 30 minute overview that also includes some nice golden rules to design by.

    Note: Normalization rules are made to be broken so don't freak out that they seem constraining or overkill. That's covered under "DeNormalization".
    Thank you, document is great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •