Simple design Question

  • Thread starter Thread starter sklett
  • Start date Start date
S

sklett

I'm redesigning my application to use ADO.net and this is also resulting in
a schema redesign - FUN!

I have a fairly newbie design question;
I have a table called Tbl_ProductInfo and it has fields like this:
ProductTypeID
ProductSN
DateManufactured

I have another table called Tbl_ProductTestResults and it has fields like
this:
TestResultID
ProductSN
DateTested


I would like to determine if a given ProductSN has been tested. There are
two ways I can think to do this;
1) Get the count of records from Tbl_ProductTestResult that have a matching
serial number
2) Add a field to Tbl_ProductInfo - 'HasBeenTested'

This is a simple example for illustration purposes. Option #2 appeals to me
because I don't need to touch any additional tables to get my answer. in my
real application I have several more circumstances like the above where I
could either store data in a central table or determine the same data by
evaluating different tables.

Hope that makes sense.

Thanks for reading,
Steve
 
Not sure where your primary keys are, but I will assume they are the ones
marked "ID". If that is the case, you have an incorrect database design. If
this is a super simple application, you may get by with this, but if it is
something you are going to scale, then you need to normalize the design. The
entities and their attributes will drive the number of tables, how many you
would like to have is not relevant to the design issue. You have multiple
normalization issues: in my book, tables are collections of entities:
Products, ProductTypes, ProductTests, etc. (the TBL_ prefix gives you
nothing and is annoying, redundant, and slows down the process of figuring
out what the table is not to mention how it affects writing queries). I can
visualize your tables like so:

Products
ProductID (PK)
ProductSN
ProductName
DateManufactured

ProductTests
TestID (PK)
ProductID (FK)
TestDate
TestResult

This gives you an unlimited number of products which can have an unlimited
number of tests. To check to see if (or how many times) a product has been
tested is a trivial query.
 
Back
Top