DB Gurus. Sort of OT.

TConnolly

Lifetime Supporting Member
Join Date
Apr 2005
Location
Salt Lake City
Posts
6,152
Not a DB guru myself, so I'm wondering if I'm going about this the right way.

I'm setting up a recipe database for a process that will contain a setpoint profile. The setpoint profile will have a variable number of setpoint segments (1 to 30), so it doesn't lend itself well to a single table. The single table approach would have 368 columns.

I was thinking of using two tables. The first table would have the following columns

  • Profile_Number (int) unique primary key
  • Profile_Name varchar40
  • High_Limit (int)
  • Number_Of_Segments (int) might not be necessary.
  • plus a couple of other parameters.


The second table will have the following columns

  • Profile Number (int)
  • Segment Number (int)
  • Time (int)
  • Setpoint (int)
  • and about a dozen other parameter columns to go with each setpoint segment.
In the second table, the combination of profile number and segment number would have to be unique (I'm not sure how to set that up yet)





I was going to query the first table by the profile number, which will return just one record. Querying the second table will return a variable number of records, one for each segment, which I will query ordered by segment number.

Is this a good DB design? Or is there a better way to build the schema, maybe a relational table or some other way?
 
what software are you using for your DB? SQL..... Access....

You can use two tables and create a primary key for each table and then relate those tables together based off a primary key in one table and creating it as a foreign key in the second table.
 
You can always use a auto-number as the unique key for your 2nd table.

or

Look up "composite primary key" which is what you are trying to do.
 
MSSQL Server.

Primary composite key. That is the term I was looking for. In the second table I want Profile_Number and Segment_Number to make a unique composite key, where there might be multiple records of the same profile number but each of those segment numbers will be unique.
 
Yes, you would want to use two tables as you described. You could do the following -

recipe
  • recipe_id
  • name
  • etc
recipe_step
  • recipe_step_id
  • recipe_id
  • step_number
  • name
  • time
  • setpoint
  • etc
You would create a composite primary key on recipe_id and step_number in the recipe_step table to prevent duplicate steps from being entered for a given recipe_id.
You would probably not use a number_of_segments column in the recipe table since it's easily obtainable from a query SELECT COUNT(*) FROM recipe_step WHERE recipe_id = 1.
Depending on your process, if you have multiple steps that use the same time and setpoints then you could go with three tables, recipe, recipe_step, step_setpoints.

I should add - The best (easiest) way to handle changing the order of steps or number of steps in a recipe would be to first delete all the steps for a recipe from the recipe_step table then insert the 'new' steps into the recipe_step table. So on the frontend you would want to load the current recipe, user edits recipe, delete existing recipe from recipe_step table, then insert edited recipe into recipe_step table.
 
Last edited:
Good discussion! I've got a recipe system too, and have similar "integrity" questions about the design side of SQL. For reference I'll define my setup, I think may need to consider the "composite primary key" too, but it might be good to compare notes.

Like you, I've determined I need at least 2 tables. Generally speaking my tables have the following format:

Table 1: Recipe Header
- ID (primary key)
- UUID (string, universal unique Identifier I create)
- Name (string)
- System (int)
- Type (int)
- CreationDate (datetime)
- Creator (string)

Table 2: Recipe Data
- ID (primary key)
- UUID (string, universal unique Identifier I create)
- Parameter (int, recipe parameter number)
- Value (float, recipe value)

For every 1 entry into the Recipe Header table, I have 1000 entries into the Recipe Data table. The UUID field is how I link the information of each database, and serves as my unique identifier. For me, my concern is how to protect this relationship at the SQL level inherently versus INSERT/SELECT/UPDATE/DELETE queries I am using in my scripting to write the recipe data to the database. Each time I create/edit/delete a recipe, I have to be sure that if I have a record in my Recipe Editor table, I should have 1000 records in my data table.

My system, upon a recipe edit deletes the old entry and replaces it with a new one. I haven't really looked ad doing an "UPDATE" on the data that is changed.

I will also mention that I have a 3rd table, for Recipe Edits, this way if I screw something up my data table isn't affected. I was looking at implementing COMMIT/ROLLBACK functionality but haven't gotten that far yet.

Fundamentally, when you have multiple tables that require information in each to work poorly, what is the best practice to ensure that integrity in the SQL table design?
 
Tark's advice above is excellent.
Some other advice, On every table you create, do create an auto increment ID, use that as the unique key (you don't have enough ever to need GUID's). Don't use any of your data fields as the table's primary key, that can get extremely messy.
 

Similar Topics

I have a dewpoint sensor the is 4-20mA out across -148 to 68 Deg F. I want to find out what the dewpoint reading should be for each mA. I have a...
Replies
5
Views
1,387
I'm having one heck of a time trying to get the screen to change automatically to the login screen when someone logs out. I essentially want the...
Replies
9
Views
2,815
Hey guys, I'm new to the PLC world, ive done lots of reading on this website and it really made me realize ive barely scratched the surface. The...
Replies
8
Views
2,221
I have hit a wall on a request I received for a change to iFix. Operations want security added to a button which resets a counter. The way it...
Replies
3
Views
1,892
  • Locked
I have hit a wall on a request I received for a change to iFix. Operations want security added to a button which resets a counter. The way it...
Replies
2
Views
3,148
Back
Top Bottom