CODEWITHSUNDEEP
sqlsql-serverdatabaserecursionrecursive-datastructures
Georgi Koemdzhiev
Georgi Koemdzhiev

Reputation: 11931

Logical Modeling difficulty

I have a task to design a simple database that will store information about restaurants, customers and Categories that each restorant falls in (ex. American,Indian...). The problem is that I am not sure how to model the database having the fact that every customer may have zero or more registered other customers as friends. Whilst a customer can have many friend connections, for each friend connection the date must be recorded. I assume that I have to create another table called Friends that will contain the cust_id,data as attributes.

My tables are:

Restaurants(rest_id,name,address,date_opened,income_cat_id*)
Category(car_id,title,Description)
Customers(cust_id,name,address,DOB,Mobile_number)

Here is my ER Diagram, but as I said I am not sure if the Recursive relation is right for my Customers table:

Here is the ER diagram

Upvotes: 0

Views: 53

Answers (2)

Georgi Koemdzhiev
Georgi Koemdzhiev

Reputation: 11931

Thank you a very much! Does that mean that I have to have the following constraints in the Connection table? 

CONSTRAINT pk_Connections PRIMARY KEY (cust_id1,cust_id2),
CONSTRAINT fk_Customers_Connections_cust_id1 FOREIGN KEY (cust_id1) REFERENCES Customers(cust_id)
                                                ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Customers_Connections_cust_id2 FOREIGN KEY (cust_id2) REFERENCES Customers(cust_id)
                                                ON DELETE NO ACTION ON UPDATE NO ACTION);

Upvotes: 0

StevieG
StevieG

Reputation: 8719

Yes, you need another table to model the connections, something like:

Connection(cust_id1, cust_id2, connectdate)

Upvotes: 3

Related Questions