CODEWITHSUNDEEP
databasedatabase-designrelational-database
niquat
niquat

Reputation: 3

Normalizing a Product Designer database

I'm looking to create a database for tracking purposes. For the purposes of this question I have abstracted it a bit and transformed it into a Product Design tracking database. I'm trying to make it as normalized and efficient as possible. Essentially I want to be able to track Employees and what designs they've participated in.The queries I want to run aren't particularly complex. I want to be able to query how many employees participated in the design of a specific model, what models and products an employee has designed, how many employees designed a product, etc. The products would be redesigned every year or half year.

My concern is how I'm managing the many to many relationship between models and employees. In the example provided I have a Design table between the Model and Employee Table. This will essentially be a dump of all Employees designers, which both resolves the many to many relationship (which I understand to be a bit of a bad thing to have) and make query design relatively simple. I also assume I can index it by either Emp_ID or Model_ID to make it more efficient.

However I'm worried this table may get a bit unwieldy over time. In its current role I could make this database very inefficient and probably not notice any degradation in performance. However I'm hoping to make this relatively scale-able as I want to make this easy to admin (whether I admin it or someone else takes over) and I'm hoping to add features over time (a CRM-like functionality for example).

I was thinking I could create a table to for each employee and track design projects by having them in a separate empID_design table, but that also seemed very unwieldy. Essentially every other way I thought up on how to do this ended up with creating a large number of tables versus inserting a Row.

One other thing was I wanted to be able to track project managers. In the current form I thought tracking it in the Design table made sense. I don't think the Project Mgr would change mid-design but is there an elegant way to track it if they did?

Any help or advice you can provide is appreciated. I'm a bit rusty with Database Design and ERD Design, so if you notice something that doesn't make sense, its more likely a mistake I made as opposed a fancy nuanced design I thought up.

To give a basic idea of what each table could be representing:

Company: Black and Decker

Product: Rotary Tool

Model: D-5230

Designed by: George Santos, Kevin Smith, John Rodes

Project Manager: Kevin Smith

Thank you in advance!

ERD Diagram: https://i.sstatic.net/flo4l.png

Upvotes: 0

Views: 129

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

It sounds to me like the intersection table between model and employee ought to be a role table, in which each row has:

  • An employee ID
  • A model ID
  • A role ID: designer, project manager, lead designer, etc

In that way an employee could even have multiple roles on a project.

Upvotes: 1

Related Questions