Member Avatar for player123

hi! i have just designed a new database and i was wondering of any could just quickly check that is passes all the first 3 normal forms.
this is an image of it:

thanks! :)

  • 3 Contributors
  • 5 Replies
  • 96 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by temiromi

Dani AI

Generated

Good progress: updated the diagram and steered the design toward the right areas. Three practical items still worth confirming in the revised model are identifier strategy, how to represent multi-valued classifications, and whether employment details are a single snapshot or a historical set. The short checklist below shows how to test the first three normal forms and a compact DDL illustrates a safe way to implement the model.

Checklist to validate 1NF–3NF in a real schema:

  • 1NF: every column holds atomic, single values; eliminate comma‑lists or repeating groups.
  • 2NF: if any table uses a composite key, ensure every non-key attribute depends on the whole key.
  • 3NF: remove attributes that depend on other non-key attributes (no transitive dependencies).
    Also enforce referential integrity with foreign keys, add unique constraints for natural identifiers (account number), and index FK columns for joins.

Example DDL pattern (illustrates stable surrogate keys, bridge table for multi-classification and a temporal employment table):

CREATE TABLE Branch (BranchID INT IDENTITY PRIMARY KEY, BranchName VARCHAR(100) NOT NULL UNIQUE);

CREATE TABLE Customer (CustomerID INT IDENTITY PRIMARY KEY, FullName VARCHAR(150) NOT NULL);

CREATE TABLE Account (AccountID INT IDENTITY PRIMARY KEY, AccountNumber VARCHAR(50) NOT NULL UNIQUE, CustomerID INT NOT NULL, BranchID INT NOT NULL, FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID), FOREIGN KEY (BranchID) REFERENCES Branch(BranchID));

CREATE TABLE AccountCategory (CategoryID INT IDENTITY PRIMARY KEY, Name VARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE AccountCategoryMap (AccountID INT NOT NULL, CategoryID INT NOT NULL, PRIMARY KEY (AccountID,CategoryID), FOREIGN KEY (AccountID) REFERENCES Account(AccountID), FOREIGN KEY (CategoryID) REFERENCES AccountCategory(CategoryID));

CREATE TABLE Employee (EmployeeID INT IDENTITY PRIMARY KEY, FullName VARCHAR(150) NOT NULL);

CREATE TABLE EmploymentRecord (RecordID INT IDENTITY PRIMARY KEY, EmployeeID INT NOT NULL, Role VARCHAR(100), StartDate DATE, EndDate DATE, FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID));

Cautions and references: prefer immutable surrogate keys for FK references, avoid wide cascade deletes (use RESTRICT or SET NULL), and keep derived values out of stored tables. For conceptual background see Database normalization and guidance on primary keys .

Recommended Answers

All 5 Replies

Member Avatar for davidcairns

A couple of suggestions

1. Ensure all tables have a PK
2. Merger EMPLOYEE with EMPLOYMENT DETAILS, they have the same pk and are 1 to 1. You could have separate tables if an employee held multiple positions over time
3. Link ACCOUNT to CUSTOMER, the customer has an account, the account has an account type

And some questions

1. Are accounts of 1 or more types?
2. Do branches have ID values?

Member Avatar for player123

thanks!

p.s.

1. yes. an account can have more than one account type.

2. no. they have branch name as their primary key.

Member Avatar for davidcairns

1. In that case have a table called AccountType table as a lookup of the different types of account and a CustomerAccountType table to join from Account to AccountType

2. Then you have the correct detail there, however I would never allow this in a real world situation, names change but a PK should never change


Good luck with the rest of it

Cheers

D

Member Avatar for player123

ok i've done some chnages to it now..
do u think it will work now:

Member Avatar for temiromi

I would like to do something similar but with different names and references can you help me out cos I am new in the system (database)

it me ur co project builder temiromi

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.