Creating a lost and found schema I have a school assignment to create a lost and found schema.


An admin can log a found item (someone turned it into the lost and found office)
A user can report a lost item
A user can claim a found item


Would the below schema work or should I somehow merge lost/claim into one table?
I would create tables for:


People
Actions
Items
Actions By People Against Items


In SQL, this would look like:

CREATE TABLE dbo.Actions
(
    ActionID int NOT NULL PRIMARY KEY
    , ActionDescription varchar(100) NOT NULL
);

CREATE TABLE dbo.People
(
    PersonID int NOT NULL PRIMARY KEY
    , PersonName varchar(100) NOT NULL
);

CREATE TABLE dbo.Items
(
    ItemID int NOT NULL PRIMARY KEY
    , ItemDescription varchar(200) NOT NULL
);

CREATE TABLE dbo.ItemActions
(
    ItemID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Items(ItemID)
    , PersonID int NOT NULL FOREIGN KEY
        REFERENCES dbo.People(PersonID)
    , ActionID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Actions(ActionID)
    , ActionDate datetime NOT NULL
);


Sample data:

INSERT INTO dbo.People (PersonID, PersonName)
VALUES (1, 'Ella')
    , (2, 'Mike')
    , (3, 'Sue');

INSERT INTO dbo.Actions (ActionID, ActionDescription)
VALUES (1, 'Lost')
    , (2, 'Found')
    , (3, 'Claimed');

INSERT INTO dbo.Items (ItemID, ItemDescription)
VALUES (1, 'Apple')
    , (2, 'Pear')
    , (3, 'Peach')
    , (4, 'Ameoba');

INSERT INTO dbo.ItemActions (ItemID, PersonID, ActionID, ActionDate)
VALUES (1, 1, 1, '2018-03-04 12:34:56')
    , (1, 2, 2, '2018-03-05 09:17:00')
    , (2, 1, 1, '2018-04-01 10:42:01')
    , (3, 1, 1, '2018-04-01 11:40:05')
    , (3, 2, 2, '2018-04-02 15:40:00')
    , (2, 3, 3, '2018-04-03 17:54:46');


Query:

SELECT i.ItemDescription
    , p.PersonName
    , ia.ActionDate
    , a.ActionDescription
FROM dbo.ItemActions ia
    INNER JOIN dbo.Items i on ia.ItemID = i.ItemID
    INNER JOIN dbo.People p ON ia.PersonID = p.PersonID
    INNER JOIN dbo.Actions a ON ia.ActionID = a.ActionID;


Results:

╔═════════════════╦════════════╦═════════════════════════╦═══════════════════╗
║ ItemDescription ║ PersonName ║       ActionDate        ║ ActionDescription ║
╠═════════════════╬════════════╬═════════════════════════╬═══════════════════╣
║ Apple           ║ Ella       ║ 2018-03-04 12:34:56.000 ║ Lost              ║
║ Apple           ║ Mike       ║ 2018-03-05 09:17:00.000 ║ Found             ║
║ Pear            ║ Ella       ║ 2018-04-01 10:42:01.000 ║ Lost              ║
║ Peach           ║ Ella       ║ 2018-04-01 11:40:05.000 ║ Lost              ║
║ Peach           ║ Mike       ║ 2018-04-02 15:40:00.000 ║ Found             ║
║ Pear            ║ Sue        ║ 2018-04-03 17:54:46.000 ║ Claimed           ║
╚═════════════════╩════════════╩═════════════════════════╩═══════════════════╝

This setup means you could add other actions such as "Thrown Away" to the list of possible actions without disrupting your design.