CODEWITHSUNDEEP
sql-serverentity-frameworkvisual-studioef-code-first
Tums
Tums

Reputation: 505

Should I normalize this database design further?

I have the following database design:

TABLE [Document]
[DocumentId] [int] NOT NULL, --Primary Key
[Status] [bit] NULL,
[Text] [nvarchar](max) NULL,
[FolderPath] [nvarchar](max) NULL

TABLE [Metadata]
[MetadataId] [int] IDENTITY(1,1) NOT NULL, -- Primary Key
[DocumentId] [int] NOT NULL,               -- Foreign Key Document.DocumentId (1:1 relationship)
[Title] [nvarchar](250) NOT NULL,
[Author] [nvarchar](250) NOT NULL

TABLE [Page](
[PageId] [int] IDENTITY(1,1) NOT NULL,     -- Primary Key
[DocumentId] [int] NOT NULL,               -- Foreign Key Document.DocumentId (1:N Relationship)
[Number] [int] NOT NULL,
[ImagePath] [nvarchar](max) NULL,
[PageText] [nvarchar](max) NOT NULL

TABLE [Word](
[WordId] [int] IDENTITY(1,1) NOT NULL,     -- Primary Key
[PageId] [int] NOT NULL,                   -- Foreign Key Page.PageId (1:N Relationship)
[Text] [nvarchar](50) NOT NULL

TABLE [Keyword](
[KeywordId] [int] IDENTITY(1,1) NOT NULL,  -- Primary Key
[Word] [nvarchar](50) NOT NULL

TABLE [DocumentKeyword](
[Document_DocumentId] [int] NOT NULL,     -- Foreign Key Document.DocumentId  (N:N Relationship)
[Keyword_KeywordId] [int] NOT NULL        -- Foreign Key Keyword.KeywordId

I'm using Entity Framework Code First to create the database.

Should I be normalizing my database design further? i.e. creating link tables between Document and Page, Document and Metadata, etc.? If so, is there a way to get the Entity Framework to do create the relationship tables for me, so that I don't have to include them in my models? I'm trying to learn to do this the right and most efficient way possible.

Thank you.

Upvotes: 0

Views: 45

Answers (1)

Ronald
Ronald

Reputation: 2882

Well I can't immediately answer your question, but I have some thoughts that might improve your design:

  1. A document (in real life, at least) can be written by more than one author. This means, that your 1:1 relationship from Document to Metadata should be a 1:n relationship (unless you can prove that there will never be a situation that there's more than one author)
  2. The title of a document is (in my view) more a property of the document than a piece of metadata (also having 1. in mind)
  3. What does this Word table do?
  4. The column Keyword_KeywordId should be called plainly KeywordId if you want to be consistent in your naming. The same applies to Document_DocumentId.

For the rest it looks pretty normalized

Upvotes: 1

Related Questions