Saurashtra University Students: Designing a Database Schema for Your Internship Project

Start with the nouns your shop app must remember (customers, products, orders), give each one a table with a primary key, and connect them with foreign keys that mirror real-world relationships. Then check the design against normalisation rules so no fact is stored twice. In a 14-day internship you can do all of this in about two days, and it will save you from rewriting queries and API code later.

Why the schema deserves your first two days

In a full stack project the database shapes everything above it. Your API endpoints, form fields, validation rules and even page layouts all depend on what you store and how the pieces relate. A weak schema shows up later as duplicate customer records, orders that cannot be traced to a product, or a report that needs five awkward joins.

A two-week internship leaves little room for redesign. A sensible rough plan looks like this:

• Days 1-2: requirements, entity list, ER worksheet, schema draft.
• Day 3: normalisation check, create tables, insert sample rows.
• Days 4-9: build the backend and frontend against the schema.
• Days 10-12: testing, fixing, small schema adjustments through migrations.
• Days 13-14: documentation, a short demo, and your final write-up.

The free two-week online internship from CareerSwami fits this rhythm well: it has 26 tracks, a free offer letter and a free completion certificate after successful completion, and a small shop app is a manageable project for the Full Stack Web Development track.

Scoping a small shop app before you draw any tables

Write the scope in plain sentences first. For the worked example in this guide, the shop app does the following:

• A visitor registers and logs in as a customer.
• Products belong to a category and have a price and a stock count.
• A customer adds products to an order, each with a quantity.
• The order has a status (placed, paid, shipped, cancelled) and a delivery address.
• The shop owner views orders and updates stock.

Deliberately leave out discounts, reviews, wishlists and multi-vendor features. Each extra feature adds tables and relationships, and you only have 14 days.

Entity-relationship worksheet for a shop app

Use this worksheet in order. Fill it in on paper or in a spreadsheet before opening your database tool.

Step 1: List the entities

Underline every noun in your scope sentences, then keep only the ones the system must remember over time. Here that gives: customer, address, category, product, order, order item.

Step 2: Fill in the attribute and key table

The table below is an illustrative schema draft for the shop app, not a copy of any official or required design.

Step 3: State each relationship in both directions

The many-to-many link between orders and products is the one beginners most often get wrong. You cannot put a list of product IDs inside an order row; you need the junction table.

Step 4: Answer the worksheet questions per table

1. What single column (or pair) uniquely identifies a row?
2. Which columns can never be empty? Mark them NOT NULL.
3. Which columns must be unique (email, category name)?
4. Which columns point at another table? Declare them as foreign keys.
5. What should happen to child rows if a parent is deleted: block it, cascade it, or set it to null?
6. Which columns will you search or filter on often? Those are index candidates.

Choosing keys, types and constraints deliberately

• Surrogate keys: an auto-increment integer or UUID as primary key is simple and stable. Avoid using email or phone as a primary key because people change them.
• Money: store prices as DECIMAL (for example DECIMAL(10,2)) or as integer paise. Floating-point types introduce rounding errors.
• Status values: use a constrained set (an ENUM or a CHECK constraint) so a typo like “shiped” cannot enter the table.
• Timestamps: keep created_at on every table; it helps debugging more often than you expect.
• Passwords: store only a salted hash from a standard library, never the plain password.
• Delete rules: prefer RESTRICT for orders referencing products, and use an is_active flag to retire a product instead of deleting it.

Worked example: from one messy table to a clean schema

This example is illustrative. It shows the reasoning, and no code here has been run against a real database.

Suppose your first instinct is a single table called orders_flat:

Problems you can spot immediately:

• The products, prices and categories columns hold lists, so you cannot filter or sum them reliably.
• Asha’s name and email repeat on every order; fixing a typo means editing many rows.
• If a price changes, you cannot tell what the customer actually paid.

The fix, step by step:

1. Split the lists so each product in an order becomes its own row. This gives you order_items.
2. Move customer details into customers and keep only customer_id in orders.
3. Move category names into categories and reference them from products.
4. Keep unit_price in order_items as a snapshot of the price at purchase time, while products.price holds the current price. This is intentional, not a normalisation failure, because the two values mean different things.

A minimal SQL sketch of the core of the result (illustrative, MySQL-style):

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock_qty INT NOT NULL DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE order_items (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Asha’s two orders now become two rows in orders pointing to one row in customers, and three rows in order_items.

Normalisation checklist you can apply in ten minutes

Run each table through these checks. If a table fails one, note the fix beside it.

Third normal form is usually the right stopping point for an internship project. Pushing further adds tables without adding value at this scale.

Testing the schema with sample data and real questions

Before building any screens, insert five customers, ten products and a handful of orders, then try to answer these questions with SQL:

1. What did a given customer order, and what was the total?
2. Which products have fewer than five units in stock?
3. Which category earned the most in the last 30 days?
4. Which products have never been ordered?
5. Can you insert an order item for a product that does not exist? (It should fail.)

If a question needs a clumsy workaround, the schema is telling you something. Fix it now, while only sample data exists.

Schema changes during the internship

You will almost certainly adjust the design around days 6 to 10. Keep this manageable:

• Save every change as a numbered migration or SQL script rather than editing tables by hand.
• Keep a seed script that rebuilds your sample data from scratch.
• Export an ER diagram image (from MySQL Workbench, dbdiagram.io or a similar tool) for your final write-up.
• Commit the schema files to a Git repository along with your application code.

Saurashtra University students: fitting a schema project into your academic context

Students at Saurashtra University in Rajkot often ask how an outside internship project relates to what their department expects. The verified picture is limited, so treat the following as a starting point.

A copy of a Saurashtra University ordinance document, shown as effective from June 2023 and hosted on a third-party college site, contains a clause on a “Summer Internship/ Apprenticeship” that students must undertake. The duration, credits and evaluation rules could not be confirmed from that excerpt, and nothing verified says whether an online internship qualifies. CareerSwami’s internship offers an offer letter and completion certificate, but it does not promise academic credit, so do not assume it substitutes for any university requirement.

If your course includes project work, as the 2020 MCA syllabus PDF hosted on sucsd.org.in indicates in its excerpt, a documented schema (ER diagram, table list, normalisation notes) is exactly the kind of artefact that strengthens a project report. Syllabi change, so check the current version for your programme.

On the career side, the university’s Placement Cell page says the Training & Placement Cell runs resume evaluation sessions for final-semester students. A short project entry such as “Designed a normalised six-table schema for a shop app with foreign keys and constraints” gives that review something concrete. The cell’s contact page lists it in the Main Building on the Rajkot campus.

Timing also matters. The news report on the 2024-25 calendar described a Diwali break and a 49-day summer vacation. That is an older year, so check the current calendar before planning your 14 days around a break.

Verification steps for Saurashtra University students

1. Ask your department office in writing whether the internship or apprenticeship clause applies to your programme and semester, and request the current ordinance text.
2. Ask your project coordinator whether an online, 14-day internship can count toward any requirement, and what duration is expected.
3. Find out which documents your department wants for internship evidence (letter, certificate, report, mentor sign-off).
4. Check the current academic calendar for your college and note exam and vacation dates against your 14-day window.
5. Ask your guide whether a database design document can be attached to your project report.
6. Email or visit the Training & Placement Cell to ask about resume review timing for your batch.

Documenting the schema in your final submission

Whoever reviews your project will probably spend a few minutes on it. Make those minutes count by including:

• An ER diagram image with cardinalities marked.
• A table of columns, types, keys and constraints (like the one above).
• A short note on three design decisions, such as the unit_price snapshot, the is_active flag and the delete rules.
• The SQL scripts and a sample query set.
• One paragraph on what you would improve with more time (for example, payments, inventory history or indexing).

Frequently Asked Questions

Should I use MySQL, PostgreSQL or MongoDB for a small shop app?

A relational database such as MySQL or PostgreSQL suits this project because orders, customers and products have clear relationships and benefit from foreign keys and transactions. MongoDB can work, but you would give up built-in relational integrity, and a schema-design exercise is easier to demonstrate with tables.

How many tables is enough for an internship project?

Five to eight well-designed tables are plenty. The shop example here uses six. Quality of keys, constraints and relationships matters more than table count.

Do I need to reach Boyce-Codd or fourth normal form?

No. Third normal form is the practical target for a project of this size. Mention any deliberate exceptions, such as storing unit_price in order_items, in your documentation.

Should the cart be stored in the database?

You have two options. A cart can be an order with status “cart”, or a separate carts table with cart items. For 14 days, reusing the orders and order_items tables with a status value is simpler.

Why not store the total in the orders table if it can be calculated?

It is a trade-off. Calculating from order_items avoids drift, while storing total_amount makes reports and receipts faster and freezes the amount charged. If you store it, set it inside a transaction when the order is placed and document the choice.

Can I change the schema after I have started coding?

Yes, but do it through migration scripts so that your code and database stay in step. Early changes are cheap, and changes after day 10 risk breaking finished features, so settle the core tables in the first three days.

Will this schema project count for credit at Saurashtra University?

That cannot be assumed. CareerSwami’s internship provides an offer letter and a completion certificate but no academic credit. Ask your department in writing whether any external internship is accepted for your programme.

What should I put on my resume from this project?

Describe what you built and the decisions you made, for example: “Designed a normalised relational schema (six tables, primary and foreign keys, constraints) for a shop app and built a REST API on top of it.” Link the repository, and keep claims to what you actually did.