Link to home
Start Free TrialLog in
Avatar of jab56
jab56

asked on

Is it possible to create a relationship that can match a key to itself(table) or to another table

Cars can tow Cars or Boats. Boats are just boats and they cannot tow anything.  Is there a way i can represent this in a database?... or is there a "bigger picture" that i should be looking at... or should my database be setup different?    

CarsTable
================  
int (key)
(..many car related fields)
int boat_or_Car_towed  <--- can referance any key in the cars table or boats table

BoatsTable
===================
int (key)
(... many boat related fields)


Notes
=======
*** I am going to use C# linq to access the data so maybe there is something that can be done on programming side instead. I want to use the build strongly-typed dataset feature.
*** I have used "Cars" and "Boats" to make it problem clearer. This is actually for functions(functions table) that can access other functions or constants(constants_table).


Solutions that i found but are not efficient...
================================
  *** Just don't create the relationship... (drawback: I would have to write extra code to maintain this relationship.  Also, in C# Linq, it would not build a strongly typed dataset.)
  *** combine the boat and car tables... (this would create unused columns and would waist space/resources)  (i have a large amount of data)


Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jab56
jab56

ASKER

angleIII,

I played around with it and this is what I was looking for. This will work with the linq dataset builder tool also.  Its even better because it does not have any special or odd sql server configurations.  Thank you very much for your help.  

Ryan
Avatar of jab56

ASKER

angleIII,

I played around with it and this is what I was looking for. This will work with the linq dataset builder tool also.  Its even better because it does not have any special or odd sql server configurations.  Thank you very much for your help.  

Ryan