locked
How can i connect... RRS feed

  • Question

  • Hi guys i have a problem with this Design can some one help me.

      In this database i have 3 different Companys called:

    Producers

    Clients

    Transporters

     

    and they all should be shown into a table called Projects with there contact details.

    now if have the time to take a look at this Photo i have at live.com

    https://skydrive.live.com/redir.aspx?cid=cdc59b233f2acd53&resid=CDC59B233F2ACD53!179 

     

    i have photo of ER diagrm of the database with my problem and the way i want it to work. please take a look at both.

    any kind will be nice just give me some idea how it should be.

    Regards

     

     

    Monday, July 4, 2011 7:03 AM

Answers

  • Hi,
    I looked through your first diagram called “DBsortout”.There is one place you need to pay attention,if  relation between Client and Producers is many to many ,you need to add one more table which contained the relation info of Client and Producers and remove producerID from Client.Please check the below image for more details:

    https://skydrive.live.com/?cid=A76DE4C8DFA31AA1&id=A76DE4C8DFA31AA1%211767&sc=photos#

    For the second diagram EVMDBnew, generally it looks OK ,but there are some places need to alter a little:
    One is wrong the relation mapping connection line, for example: Companys and companyTypes are one to one relation. So the one to many mapping connecting line is not appropriate here.And if Price Lists related with Companys, PriceListId  should be the  FK of Companys.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, July 6, 2011 3:26 AM
    Answerer
  • Hi

    For data integrity and independency, the relation table like 'ProducerClientRelation' needs to be created between Clients and Producers as I suggested. They could not be put into one table because they have relation with each other and not independent.
    "In the PROJECT table i want to see Client Name and Producer for that Client and having the ability to change to other Producers as well"
    What is relationship between PROJECT and Client? One to Many, Many to Many or One to One?
    If it is one project has one client, you could add clientId of Client table as a FK of PROJECT table. So you could select the related client, producer and project information by joining table Project ,ProducerClientRelation ,Producer and Client.


    Hope this helps.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, July 6, 2011 7:56 AM
    Answerer
  • 1.  first lets understand that This is a parent and child relationship

    suppliers, customers and shippers are parents of the 

    contact tables - the one you refered as project tables but let me refer to it as contatct for clarity

     

    2.  A contact may have more than one parent. Microsoft for example supplies me with software but it buys "office supplies" from us

         thus ms has a foreign key from supplier table and a foreign key  from the customers record. ms is both my supplier and customer Then   column shiiperid would be null because MS is never a shipping company

     

    3. a contact may exist even if it doesnt have a parent. for example i want to keep contact of my lawyer which is

    not my supplier, not my customer and not my shipper

     

    To establish this relationship between contacts and suppliers, shippers and customer

    you need to add 3 columns on the contacts table which are supplierid, shipperid and customerid

    which will serve as a "nullable" - "foreign key"  of the said 3 tables 

     

    To fully understand this you need to understand foreign key concept  found on this link

    http://msdn.microsoft.com/en-us/library/ms175464.aspx

     

    and the concept of nullability found on this link

    http://en.wikipedia.org/wiki/Null_(SQL)

     


    http://joeydj.com
    Wednesday, July 6, 2011 3:44 PM

All replies

  • one more thing.

     

    Every Client can have many Producers

    and every Producer can have Many Clients

    i want to have every company in one table and and there contacts in one table.

     

    now the Transporter does not have any thing to do with this except to just pick and deliver the orders

     

    please let me know if you need some info.

    thx

    Monday, July 4, 2011 7:19 AM
  • basically this equates to

    suppliers, customers, and shippers

    and you want a centralized contactlist called project tables

    in the project tables add supplierid, customerid and shipperId as a nullable foreignkey from each table

    The tables granularity  or level of uniqueness is (contactid,supplierid, customerid and shipperId )

     That should solve the problem. You might also want to add an is_active column

     


    Monday, July 4, 2011 2:09 PM
  • Hi JoeyDJ, thanks for the reply, but can you please make it little more clear for me. 

    i really appreciate your help...

    Regards

    Monday, July 4, 2011 7:50 PM
  • Hi,
    I looked through your first diagram called “DBsortout”.There is one place you need to pay attention,if  relation between Client and Producers is many to many ,you need to add one more table which contained the relation info of Client and Producers and remove producerID from Client.Please check the below image for more details:

    https://skydrive.live.com/?cid=A76DE4C8DFA31AA1&id=A76DE4C8DFA31AA1%211767&sc=photos#

    For the second diagram EVMDBnew, generally it looks OK ,but there are some places need to alter a little:
    One is wrong the relation mapping connection line, for example: Companys and companyTypes are one to one relation. So the one to many mapping connecting line is not appropriate here.And if Price Lists related with Companys, PriceListId  should be the  FK of Companys.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, July 6, 2011 3:26 AM
    Answerer
  • Hi Peja Tao,

    Thank you very much for taking the time to answer.

     the first photo you have looked is the small picture of the last photo EVMDBnew.  i was having very hard time putting Clients and Producers in the same table so i try to look for more subtle  approach.

    Do you think i can use the diagram you have made into my EVMDBnew.

    in the PROJECT table i want to see Client Name and Producer for that Client and having the ability to change to other Producers as well.

    please let me know if it is possible.

    i really appreciate the help.

    Best Regards


    • Edited by Peja TaoEditor Wednesday, July 6, 2011 7:23 AM miss spelled
    Wednesday, July 6, 2011 6:56 AM
  • Sorry I miss spelled your name :(
    Wednesday, July 6, 2011 6:57 AM
  • Hi

    For data integrity and independency, the relation table like 'ProducerClientRelation' needs to be created between Clients and Producers as I suggested. They could not be put into one table because they have relation with each other and not independent.
    "In the PROJECT table i want to see Client Name and Producer for that Client and having the ability to change to other Producers as well"
    What is relationship between PROJECT and Client? One to Many, Many to Many or One to One?
    If it is one project has one client, you could add clientId of Client table as a FK of PROJECT table. So you could select the related client, producer and project information by joining table Project ,ProducerClientRelation ,Producer and Client.


    Hope this helps.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, July 6, 2011 7:56 AM
    Answerer
  • Hi again,

                   What is relationship between PROJECT and Client? One to Many, Many to Many or One to One?

    Client to Project           many to many

    Client to Contact          one to many

    Producer to Project       many to many

    Producer to Contact      many to many

    transporter to Project    many to many

    Transporter to contact   one to many

     

    say like this client Apple from New york, Denver, California etc can have one project Nr. 2020  and one Client can have many different Projects.

     

    i have taken your advice and made a Producer Client Relation table and it solved my one of the problem.

    thank you so much. :)

    i have posted the new diagram.

    https://skydrive.live.com/redir.aspx?cid=cdc59b233f2acd53&resid=CDC59B233F2ACD53!179

    please can you take a look at Contact table as well. i think i am not putting the data the right way. any suggestion.

     

    best regards

    Wednesday, July 6, 2011 12:50 PM
  • 1.  first lets understand that This is a parent and child relationship

    suppliers, customers and shippers are parents of the 

    contact tables - the one you refered as project tables but let me refer to it as contatct for clarity

     

    2.  A contact may have more than one parent. Microsoft for example supplies me with software but it buys "office supplies" from us

         thus ms has a foreign key from supplier table and a foreign key  from the customers record. ms is both my supplier and customer Then   column shiiperid would be null because MS is never a shipping company

     

    3. a contact may exist even if it doesnt have a parent. for example i want to keep contact of my lawyer which is

    not my supplier, not my customer and not my shipper

     

    To establish this relationship between contacts and suppliers, shippers and customer

    you need to add 3 columns on the contacts table which are supplierid, shipperid and customerid

    which will serve as a "nullable" - "foreign key"  of the said 3 tables 

     

    To fully understand this you need to understand foreign key concept  found on this link

    http://msdn.microsoft.com/en-us/library/ms175464.aspx

     

    and the concept of nullability found on this link

    http://en.wikipedia.org/wiki/Null_(SQL)

     


    http://joeydj.com
    Wednesday, July 6, 2011 3:44 PM
  • Hi,
    For the relationship of many to many, I suggest to create the relation table like ProducerClientRelation which contains
     2 related tables ‘primary key as its foreign keys and their shared information.
    And for one to many relationship, such like one Client to many Contact, client ID should be in Contact table as a foreign key.
    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, July 7, 2011 2:47 AM
    Answerer
  • Hi,

             thanks for the brief explanation. i have uploaded a new photo according to this, can you Please have a look at it.

     

    https://skydrive.live.com/redir.aspx?cid=cdc59b233f2acd53&resid=CDC59B233F2ACD53!179

     

    and just wanna say to every one, that it was great help guys. guys like me learn so much from this. thank you very much for your time and effort every buddy.

    my best Regards

     

    i will just wait for the last reply after you have seen my diagram please hit me back...

    Thursday, July 7, 2011 5:17 AM
  • Hi,

    Your newly edited diagram looks fine. Please go ahead.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, July 8, 2011 6:08 AM
    Answerer