SQL Server 2008 Express
I am designing a database which would have a table (containing contact details like Contactid name Number and address)
I want to add notes every time when a contact visits my office, with date and time.
So I created another table visit (Visitid, Visit date, Notes, Contactsid)
But I am unable to create a relationship as one contact can have multiple notes.
What I want is?
When I select a contact in the UI then it shows me the visit history and option to enter new visit notes
I don't quite follow - you should be able to definitely establish a foreign key relationship between Visit and Contact like this:
ALTER TABLE dbo.Visit ADD CONSTRAINT FK_Visit_Contact FOREIGN KEY(ContactID) REFERENCES dbo.Contact(ContactID)
This links each visit to exactly one contact - but each contact can have any number of visits, of course.
To get all visit for a given contact, when you select it in your UI, you'd use something like:
SELECT VisitID, VisitDate, Notes FROM dbo.Visit WHERE ContactID = @ContactID
and then you'd have to set the @ContactID parameter to the proper value (the ID of the contact chosen in your UI) before executing this SELECT.