How best to achived adding ad hoc rows to a table

Hi All

I have a small section of an app that allows users to add parts to a job, so I have several tables a Job Table, a Part Table and a Job_Part table that links jobs to parts.

There is a requirement to add ad hoc items not in the parts table like consumables for example, and I can't figure out the best way to do it. The data has to end up in the Job_Part table, and I don't really want to add to the Part table and have to generate a part No.

The row would be effectively orphaned in the part_Job table. The app is not big so some  leeway on strict performance design

Does anyone have an idea on how to best achieve this?

Andy GreenAsked:
Who is Participating?
Andy GreenConnect With a Mentor Author Commented:
Its not so much the nitty gritty of the sql rather a best way to approach this.

The job table has a Job_ID, NAme, Description etc.
The Parts Table has Part_ID, Description etc
The Job_Part table is a joining table with Job_ID, Part_ID, Quantity, Comment

I want to be able to add ad hoc part rows to the Job_Part Table without having them in the Part table.

Wondering if an approach could be to have 2 Job_Part tables, one for Parts with part numbers and the other for ad hoc parts with no part number. I could union to get the data back. Hmm the more I think about this, it could work for m.

Pawan KumarDatabase ExpertCommented:
You can use T-SQL for that. There are multiple ways for this like IF EXISTS, NOT EXISTS , LEFT JOIN etc..
Could you please show some sample data and the expected output you need.
Andy GreenAuthor Commented:
This was how I achieved this in the end. No other suitable replies.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.