-2

So I’m attempting to teach myself databases and SQL, and I’m trying to play around with making a database in management studio, and I have a question regarding recursive relationships in a table. Say I have a table called ‘Customers’ and in that table I have an int called Customer_ID as the primary key that is also an identity incrementing by 1, an nchar(125) called ‘Customer_Name’, and another int called Customer_Parent_ID (I don’t know whether I should make this an identity or not). How do I go about forming the relationships that there are customers, and I want to track that some of those customers may be parents of other customers (Think of companies, for example say both Microsoft and LinkedIn are customers, but Microsoft is also LinkedIn’s parent company and I want to show that relationship). I attached a picture of what I THINK it should look like… but again, total newbie here and any recommendations would be much appreciated.

Thank you so much!

enter image description here

EDIT: Added SQL Code and removed accidental mysql tag.

enter image description here

JDawg848
  • 121
  • 2
  • 10
  • 1
    The diagram does not show the relationship definition. It would be best if you could provide the create table and create index SQL code. That said it looks like you have it set up correctly. You dont want the foreign key (parent) to be identity. I recommend you research unary relationships, the standard model is Employee / Manager. You can also research recursive CTE's (if these are available in MySql) – Joe C Jun 08 '17 at 14:14
  • 1
    Can a customer only have one parent? – Deltharis Jun 08 '17 at 14:15
  • I added the SQL Code, and also I'll look that up now! Thank you! Accidentally clicked the recommended tag that said mysql, Im working just in Management Studio. I didnt actually think about that, I guess yeah actually if one company was to get bought by another company and now has a new parent! Interesting! – JDawg848 Jun 08 '17 at 14:35

1 Answers1

1

I think your question is somehow similar to this one, and the simple answer is you set a foreign key on Customer_Parent_ID column and refers to the Customer_ID column, so any id number that appears in the Customer_Parent_ID must also present in the Customer_ID column.

Bubble Bubble Bubble Gut
  • 3,280
  • 12
  • 28