School marks database with "mark categories" I'm trying to build a database to manage my school marks. Here in Germany we have different "categories". For example we have one category for all our written work and one for our tests. I want to have a defined number of "categories", each of them has its own list of marks. How do I have to design my database? I have tried the following:

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
    [TestsId] INT NOT NULL,
    [WrittenId] INT NOT NULL,
)


I come from C#, so I might not think like a SQL-Developer.
Marks are related to categories and subjects, so you need a SubjectId column in the Marks table so that you know what subject each record relates to.

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [SubjectId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
)


It seems as if you were envisioning creating separate category records for every subject (i.e., you would have a tests category for writing, and a tests category for math), but you should just need one category of each type of category (tests, written, lab, etc.).  The categories get tied to the subjects by the marks table, which links to both of the other tables.