How would i create the tables for this information; the dmr and tuner are products but there is going to be like 200 dmrs 50 tuners they each have different serial numbers how would i track each tuner and dmr with different serial numbers?
dmr firmware version
dmr hardware version
battery serial number
pcb firmware version
pcb hardware version
Or the easier way, create an ERD, then work out which attributes with each entity. So you could give us a clue by stating what the dmr actually is, what the tuner is - what does it tune? engines, radio frequncies, etc. What do they do?
Is there any link between the dmr and the tuner (eg does a dmr use a tuner, or vice versa, or use more than one) or are they totally separate items? like a car and an aircraft. You may know your data domains and field types, but we don't, yet.
But then if you add more product types, you'd keep having to add more tables, and it would get messy. The first way would handle any number of products types, each with a different number of different attributes.
The starting opint: Product has a many to many relationship to attribbute, so we make an link entity
product is one to many to productattribute Attribute is one to many to productattribute
There are likely to be several fields that every product has in common, such as a name, manufacturer, stock level, weight, and the like, which would go in the product table itself. The productattribute table servers to handle all the other attributes that are only in some products - frequency range, volume control, rack size, battery powered, and so on. And the product's pid links with the attribute's aid (which will be the joint natural primary key in the productattribute table, ie both its columns form the key) to link through to the attribute in question.
Here's another example - a person has children. Do you invent the fields child1, child2, child3, child4, child5, child6, and thus fail if someone has seven childern, and have lots of blanks if they have none, and have emmense problems trying to find everyone who has a child called john (and your query has to query child1, child2, chld3, child4, child5, child 6.
person--<thekids>--kidsname solves it easily. And querying for all the johns is too.