Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Could you please help me with the following problem? I'm a total novice in DB design.

I have a table with researchers who visit our university. They come for some period of time (from, till). Now, how can I save in the database if somebody came several times? I have an entry already for a particular person and need to add some other stays and be able to see them in a form.

My idea was: this must be a many-to-many relationship. Ok. Then I need a third table Researcher/Stay. And then I don't know what to do next. In Stay-table "from+till" is a key. I haven't seen examples with double-value key in a many-to-many relationship and don't know what to do next.

enter image description here

share|improve this question

2 Answers 2

Based on your diagram above what you have is a one to many, not a many to many relationship. You have your Researcher table and your Stay table. If you add the researcher id in the stay table to the primary key you have a relationship of:

Researcher.ResearcherId -> Stay.ResearcherId

Where a given researcher can have multiple stays. For example

Researcher John Smith has an Id of 2.

In the stay table you would have

From       Till       ResearcherId
1/1/2013   1/5/2013   2
2/6/2013   3/1/2013   2

etc

share|improve this answer

To further Kenneth Fisher's answer, the following shows two tables, one for Researchers, and one for their stays. Multiple stays are allowed for each researcher.

CREATE TABLE Researchers (
    ResearcherID INT NOT NULL PRIMARY KEY CONSTRAINT 
        PK_Researchers IDENTITY(1,1)
    , ResearcherName NVARCHAR(255)
);

CREATE TABLE Stays (
    StayID INT NOT NULL PRIMARY KEY CONSTRAINT PK_Stays 
        IDENTITY(1,1)
    , StayFrom DATETIME
    , StayTo DATETIME
    , ResearcherID INT NOT NULL CONSTRAINT FK_Stays_ResearcherID 
        FOREIGN KEY REFERENCES Researchers (ResearcherID) 
        ON DELETE NO ACTION 
        ON UPDATE NO ACTION
);

INSERT INTO Researchers (ResearcherName) VALUES ('Max');
INSERT INTO Researchers (ResearcherName) VALUES ('John');

INSERT INTO Stays (StayFrom, StayTo, ResearcherID) 
    VALUES ('2013-09-01T00:00:00', '2013-09-15T00:00:00', 1);
INSERT INTO Stays (StayFrom, StayTo, ResearcherID) 
    VALUES ('2013-09-20T00:00:00', '2013-10-05T00:00:00', 1);

INSERT INTO Stays (StayFrom, StayTo, ResearcherID) 
    VALUES ('2013-09-03T00:00:00', '2013-09-05T00:00:00', 2);
INSERT INTO Stays (StayFrom, StayTo, ResearcherID) 
    VALUES ('2013-10-02T00:00:00', '2013-10-05T12:00:00', 2);

SELECT * FROM Researchers;
SELECT * FROM Stays;

Results:

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.