8 Replies Latest reply: Apr 27, 2013 10:35 AM by Mindmap RSS

    Is it fine if I have 3 mutual exclusive FK in one table

    Mindmap
      Hello,
      Can you please have a look at this image:
      fam-erp.com/Capture.PNG


      The three tables COM_SALE , RES_SALE , PLOT_SALE share the same details in the table SALE_DEALS.
      Is it fine to have a FK for each one of these three tables in SALE_DEALS Table. Which means each record in SALE_DEALS Table will have two null values ???
      Or I have to MAKE IT three corresponding TABLES COM_SALE_DEAL , RES_SALE_DEAL , PLOT_SALE_DEAL for each table of : COM_SALE , RES_SALE , PLOT_SALE ..??

      Keeping in mind that under SALE_DEAL Table there are another two tables : PAYMENTS and DOCS. Which means If I go with creating new tables COM_SALE_DEAL , RES_SALE_DEAL , PLOT_SALE_DEAL ----> then I have to create more 4 tables as following:

      COM_PAYMENTS and COM_DOCS
      RES_PAYMENTS and RES_DOCS
      PLOT_PAYMENTS and PLOT_DOCS

      I hope that my question is clear.. I use Oracle 11g SOE ....

      Regards,
      Fateh
        • 1. Re: Is it fine if I have 3 mutual exclusive FK in one table
          sybrand_b
          You seem to be dealing with one type and three subtypes.
          You can implement this as three separate tables (which you have done now), or as one table with three distinct views on top of them.
          Given you need more tables dependent from the sales table, the solution you propose is completely undesirable.
          IMO, you should implement the tables as views on one common table.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Is it fine if I have 3 mutual exclusive FK in one table
            rp0428
            >
            The three tables COM_SALE , RES_SALE , PLOT_SALE share the same details in the table SALE_DEALS.
            Is it fine to have a FK for each one of these three tables in SALE_DEALS Table. Which means each record in SALE_DEALS Table will have two null values ???
            >
            No - that indicates that your data model is flawed.
            >
            Or I have to MAKE IT three corresponding TABLES COM_SALE_DEAL , RES_SALE_DEAL , PLOT_SALE_DEAL for each table of : COM_SALE , RES_SALE , PLOT_SALE ..??
            >
            No - that also indicates that your data model is flawed.

            The 'type' of property (e.g. residential, commercial, plot/bareland) is an ATTRIBUTE of the property.

            You need to redesign your data model to normalize it properly.

            The attributes of a sale (e.g. date sold, amount, purchaser, terms) are independent of the type of property.
            • 3. Re: Is it fine if I have 3 mutual exclusive FK in one table
              Mindmap
              Thanks All,
              I understand that a SALE details are the same for Commercial & Residential properties. But, How it should be ??
              Starting from the beginning: -- Before speaking about the Closed Deals details ....
              I have three types of properties :
              Com , Res , and Plots each type requires one dedicated table because each type has its own details..... We agree on this.
              Then as I get from you. I have to store the sale marketing  information that is common for each type Com, Res , Plot in one table SALE_MARKETING which has PROPERTY_TYPE as an attribute which accepts the values of COM , RES , PLOT........
              OK, the question is:
              how to reference the FKs on SALE_MARKETING Table into COM , RES, PLOT Tables ???

              Regards,
              Fateh
              • 4. Re: Is it fine if I have 3 mutual exclusive FK in one table
                sybrand_b
                Com , Res , and Plots each type requires one dedicated table because each type has its own details..... We agree on this.
                Incorrect,
                In my reply, which you apparently didn't read, didn't want to read, or didn't understand, I stated you shouldn't do it this way, as this approach is a disaster in waiting.
                I know my solution works. Your flawed solution is not going to work, EVER.

                --------------
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: Is it fine if I have 3 mutual exclusive FK in one table
                  Mindmap
                  Hello Sybrand,
                  Please do not get me wrong. I checked the thread in the break of Business Intellgance workshop where the time was tight. I highly appreciate your help ....
                  you should implement the tables as views on one common table.
                  I did not get you. Can you please explain .

                  it is Real-Estate DB :
                  Why having 3 tables to store the Data of Res , Com , and Plots is wrong thing ?

                  What I Have know which is disaster :

                  RES (ID PK ...)
                  RES_SALE (ID PK... RES_ID FK)
                  RES_RENT (ID PK... RES_ID FK)
                  -------------------
                  COM (ID PK ...)
                  COM_SALE (ID PK... COM_ID FK)
                  COM_RENT (ID PK... COM_ID FK)
                  -----------------
                  PLOT (ID PK ...)
                  PLOT_SALE (ID PK... RES_ID FK)


                  Then I need tables to store the data of Sale Deals and anther table to store Rental Deals.
                  Also, I need a table to store the Docs of each deal.


                  Can you please advice ....

                  Best Regards,
                  Fateh
                  • 6. Re: Is it fine if I have 3 mutual exclusive FK in one table
                    sybrand_b
                    Ok,

                    What I am working on myself is a database to track music, including artists.
                    So I have performing individuals, I also have composers, and writerss about music, but I also have ensembles.
                    Initially I had persons (both writers, performers and composers) in one table, and ensembles in another one.
                    Now I want to know who contributes to which CD, and to get out a list of contributors I needed to join with both ensembles and persons.
                    This was a disaster, as I expected.

                    So,
                    I made one table CONTRIBUTOR, with all attributes from both tables (most were common)
                    and a view PERSON on top of CONTRIBUTOR, and a view ENSEMBLE on top of CONTRIBUTOR.

                    What I have is an example of several objects which all inherit from a common base.
                    In OOP this is called polymorphism.

                    Your situation is the same.
                    You have polymorphism.

                    In my case I would need to

                    select * from media,
                    (select ...
                    from person
                    union
                    select * from ensemble
                    )
                    in the solution with separate tables.

                    You are going to have the same pain.

                    Hence my suggestion to merge everything in one underlying table, with one set of FKs, and to put views (RES, COM and PLOT) on that common table.

                    -----------
                    Sybrand Bakker
                    Senior Oracle DBA
                    • 7. Re: Is it fine if I have 3 mutual exclusive FK in one table
                      Mindmap
                      Ok, I understand .. Thanks .. As DB design is critical, soon I will re-post clear example about my case. Hopefully, I get confirmation before building my App...
                      • 8. Re: Is it fine if I have 3 mutual exclusive FK in one table
                        Mindmap
                        Hello,

                        Since this thread was closed: this is a related question: {thread:id=2530011}

                        Regards,
                        Fateh

                        Edited by: Fateh on Apr 27, 2013 8:35 AM