Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    Question Unanswered: Multiple Table Relation Design

    I'm curious whether my concept of database design is correct. Here is my case:

    Case #1

    Usage for a car lot.

    Sales, Accounts, CashSales

    Sale_ID, Time_Sold

    Account_ID, Sale_ID

    CashSale_ID, Sale_ID

    The concept is that redundancy is eliminated by keeping Time_Sold in Sales, and all operations based on Sales (Sales for the month, total sales etc.) can be executed with a simple query.

    The problem is that, depending on interface design (i.e. PHP site), an Account and a CashSale can have the same Sale_ID... Which is incorrect!

    My question is this: is this proper design? It doesn't seem that way.

    Also, what follows is my other choice for a solution, and the same question still applies.

    Case #2

    Sale_ID, Type ENUM('Account','CashSale'), Related_ID



    The concept is that each Sale is either an Account or CashSale and queries would be based around this fact. The only problem is determining which table each record is in. In order to do this, you must check whether the Type is an Account or CashSale. My question in this regard is, is this correct to discern related tables based on data in a record?

    1) Is the design of Case #1 correct?
    2) Is the design of Case #2 correct?
    3) Is it correct to determine which table to used based on data in a record?


  2. #2
    Join Date
    Nov 2005
    This only attempts to answer Case #1. Based on what I understand from your question, it is okay to have Sale_ID in two different tables as long as you have those relationships defined.
    Programming/Web Design

    Offer solutions-not criticism.

Posting Permissions

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