Table Structure
I use a "one-to-many" kind of table structure. Instead of storing all information in one table, I like to split it up.
For instance, I work in the battery profession and we record a lot of unique characteristics of a battery each time we service it.
Example;
Connector Type
Case Color
Cable Length
Cable Size
Model Number
ect. ect.
All in all, I have 22 pieces of information I record each time a new battery is inserted.
So this is how the process works;
Table #1 Systems
Structure
SystemID
SerialNumber
Batteries all have unique Serial Number so that is my Index. When I enter a new "System" IE Battery it goes into a record like this
SerialNumber = "00001"
SystemID = "Battery-00001"
(Identifier because we also work with the chargers, so I need a way to identify)
Table #2 SystemsAllocatedParts
SystemID
PartID
Data
So each time a new battery + System is added, we record those 22 items, those 22 items are now stored in SystemsAllocatedParts that way I can run a query against the SystemsAllocatedParts and get all information in one place.
IE;
SystemID = "Battery-00001"
PartID = "BatteryData-ConnectorType"
Data = "SB 350 Gray"
So for each of these items I need to do an INSERT statement to put it into AllocatedParts. I usually handle INSERTS like this
Code
Statement = "INSERT INTO SystemsAllocatedParts (SystemID, PartID, Sensor) VALUES (@Sys, @Part, @Data)"
Using con As New MySqlConnection(DBConnection)
Using cmd As New MySqlCommand(Statement)
Using sda As New MySqlDataAdapter()
con.Open()
cmd.Parameters.AddWithValue("@Sys", "Battery-" & SerialNumberToUse)
cmd.Parameters.AddWithValue("@Part", "BatteryData-UnitNumber")
cmd.Parameters.AddWithValue("@Data", unitnumberBox.Text)
cmd.Connection = con
sda.InsertCommand = cmd
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Using
But I do NOT want to make 22 insert statements this way.
Does anyone know of a way I can handle all of these, where I change the PartID + the Data for each of the items?
Willing to pay upwards of 500FG for a good answer. PM me if anything doesn't make sense.