locked
One to Many - One to One Relationship ? RRS feed

  • Question


  • HI ALL ,

    Customer
    cust_ID,cust_Name,Cust_Phone,Cust_StartDate

    Customer_Account
    Acc_ID,Cust_ID[ refereces Customer[Cust_ID ],Acc_Name,Acc_Email,Acc_PseudoName,Emp_ID[Refernces Employee[Emp_ID]

    Employee
    Emp_ID Emp_Name Emp_Desig

    Would be my sample DB.Table structures .

    We have one to many relationship with Customer and Customer_Account.

    One to one relationship between Customer_Account and Employee .

    Then we can see we have a one to many relation ship on Customer and Employee Indirectly .

    So far every thing is doing Good .

    Will have to develop an asp.net application to know which employee is working for which Customer [ it would be updated daily  ] . I should not disturb Customer and employee Masters stated above .

    We will have one Main employee and might be  two/NULL [no]  supporting employees working out for each customer.

    Can any expert please point me in designing the data table structures to Accomplish my Task .

    Please Note that i'll just have to use the keys of customers and Employees and not to disturb the above three mentioned datatables .

    I'm looking out for a seperate Table sturctures which just uses the Keys of Employees and Customers and accomplish my task .

    Please let me know if i'm no clear .

    Any pointers would be really appreciable .

    Thanks in Advance .

    Good Day.

     

     


    Rajkumar Yelugu
    Wednesday, August 18, 2010 1:17 PM

Answers

All replies

  • Well based on your narrative, please take a look at design of Northwind database (it was shipped with SQL Server 2000) but I am sure   you can find the installation only of that database on internet
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 18, 2010 1:25 PM
  • HI URI Dimant ,

    Thanks for your concern towards the posting.

    Northwind Structure did'nt helped me , It would be great if you have anything more to point .

    Thanks .


    Rajkumar Yelugu
    Wednesday, August 18, 2010 3:45 PM
  • Hi

    Generally it is pretty hard to help for database desining because only you know the business requirements and to get into via internet not possible ..

    Take a look at below link

     http://www.databaseanswers.com/data_models/index.htm ------ examples database design

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 19, 2010 4:31 AM
  • Hi URI Dimant ,

    The link was very helpful for me .

    But it didnt serve my purpose , I'm still got stuck at this .

    It would be appreciated if you can post a sample structure according to my requirement  which could  point me in a right direction .

     

    Thanks .

     

     


    Rajkumar Yelugu
    Thursday, August 19, 2010 6:49 AM
  • Just based on your narrative you do not nee something special for main empl and his/hers helpers. Just like a regular employees,  you are on the right task


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 19, 2010 7:13 AM
  • URI,

    Thanks for your time .

    Customer
    cust_ID,cust_Name,Cust_Phone,Cust_StartDate

    Customer_Account
    Acc_ID,Cust_ID[ refereces Customer[Cust_ID ],Acc_Name,Acc_Email,Acc_PseudoName,Emp_ID[Refernces Employee[Emp_ID]

    Employee
    Emp_ID Emp_Name Emp_Desig

    Okay then can i create a table With the following structure ?

    Customer_Employees

    Cust_ID[refernces Customer[Cust ID] ,

    ACC_ID[References Cust_Account[Acc_ID],

    Main_EmpID[Refernces Employee[Emp_ID]],

    Supp_EmP1_EmpID[Refernces Employee[Emp_ID]],

    Supp_Emp2_EmpID[Refernces Employee[Emp_ID]]

    Thanks .

     

     


    Rajkumar Yelugu
    Thursday, August 19, 2010 7:47 AM
  • Yep, just test it before applying to the production
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 19, 2010 7:54 AM
  • Okay will work on this and update the post accordingly .

    Thanks for your concerns .


    Rajkumar Yelugu
    Thursday, August 19, 2010 7:55 AM