Design suggestions? 1

[utc13]

I have a table that logs in incoming samples. Each record represents 1 sample that has been logged in. It has around 10 fields that track different pieces of information regarding each sample. One of these fields tracks a bar code that is assigned to each sample as it comes in. The problem I have is that now each sample can have multiple bar codes. I want to be able to track and associate all bar codes with its corresponding sample record but I'm undecided on how to do it. I could just put them all into 1 field and separate them with a comma or something but that would get messy and it would require that I change the field type from numeric to text. Can anyone suggest a better way to design/handle this? Any advice is much appreciated.

 

[jhall156]

This is a classic example of a need to normalize data. The key question you need to ask now is this: Do the other columns of data remain the same if you create a second record for an additional barcode?


This is likely the situation.

You need 2 tables. One for samples and one for samplebarcodes. The second table may only need to have columns for the sampleid (or whatever your primary key is in the sample table and which will act as a foreign key for the many side of a one-to-many relationship) and the barcode. The question above will tell you if any of the other columns belong to the samplebarcode table. Basically if any of the data changes by barcode (other than the barcode itself) then you need to associate it with the barcode.

Then you query the data like so:

SELECT a.sampleid, a.othercol, b.barcode
FROM samples a INNER JOIN samplebarcodes b ON a.sampleid = b.sampleid
WHERE someconditionistrue




Check online for articles/help on data normalization.

JHall