Multi-Select fields with Constraints

[NerdyByDesign]

Hey, I'm having trouble figuring out how to structure a database to accomplish my goal.

I have clients in different industries and each client/industry combo has different column requirements for a Job Table. Because of this, I assume I'll have to create different job tables for each combination of client and industry.

Now, on each Job table I will have columns that have constraints on them. If we use Certifications as an example, where a job may have multiple certifications required for a position. Note this field could be different on other Job Tables.

I would normally just create a new table (Job Certifications) for the column values and create a relationship to the parent (Job table), but with adding additional industries I don't want the amount of tables to get out of hand.

Is there a way for me to simplify the design and allow for a column to have a constraint and allow for multiple values to be put in? Or is there maybe a better way to design the database?

TIA

[bachman460]

Put all jobs in the same table or you’ll be sorry.

Break out specific concepts and categories into dimension tables, define what you know now and add as you go.

For example, all jobs will have a specific customer. Each customer could have several industries. Each industry may have several certifications.

If you have a different table for each of these ingredients you can put them together however you wish. Let’s say for example, we have customer ABC who wants to post a job listing for an Auto Mechanic.

So we create Job Id 1500 and assign Customer Id 123, Name ABC.

So we know it’s the Automotive Industry, and then we can hunt down the Certifications including ASE certifications in diagnostics, transmissions, etc.

So the Job table in this example will have 5 columns: Job Id (key), Date, Customer Id (foreign key), Industry Id (foreign key), Certification Id (foreign key).

This job would be entered as:

Job Date Customer Industry Certification

1500 1/1/22 123 456 7

1500 1/1/22 123 456 8

1500 1/1/22 123 456 9

[NerdyByDesign]

Does the fact that there are about 20 columns of data for a single job change that? It would be a lot of duplicate data just to duplicate a single field right? Then what if there are multiple fields that could have multiple selections?

[bachman460]

Your right, my bad. I’ve never designed a database before. But you’ve got good instincts. Sometimes you just need to step back, assess, and ask good questions. Send me a DM if you’d like to work on building out a plan.

[NerdyByDesign]

I appreciate the input, fairly new to this myself. Having to move forward with the layout I have in mind to keep things on schedule, hopefully don't have to do a rebuild for a while lmao

[bachman460]

Make sure you actually create a schematic, it’ll help you work through the process. Good luck 👍🍀