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 id
dmr firmware version          
dmr hardware version          
battery serial number   
pcb firmware version
pcb hardware version  

tuner id
serial number
firmware version
hardware version
3 Years
Discussion Span
Last Post by brandon66

Normalise the data.

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.

So give us a clue.


The DMR and Tuner are both different products. The tuner is used to tune the DMR, DMR receives a sattelite signal.


what database software do u intend using.and keep in mind the advice fromn drjohn.your ERD.


I plan to use MySQL. The information needed to track is there^ but they dont relate in any way. There is just those two products now but more will be added later.

product {pid, pname}
attribute{aid, aname, value}

would work.
This enables any product to have any number of attributes.

Edited by drjohn


one table for dmr, another table for tuner.

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.

Edited by drjohn


So for an ERD; Product has a one to many relationship with attribute, and a one to one with productattribute, and one to one with product attribute and attribute?


The starting opint:
Product has a many to many relationship to attribbute, so we make an link entity

product --<productattribute>--attribute

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.


So it would be something like this?

Product_ID  Product_Name    Product_SN
1           DMR             1.12235E+11
2           Tuner           12345
3           DMR             1.12235E+11
4           tuner           123456

Attribute_ID    Product_ID  
1               3   
2               1   
10              2   
12              2   
5               3   
8               1   

Attribute_ID    Attribute_Name      Value
1               DMR_FW_Version      122
2               DMR_FW_Version      123
3               DMR_FW_Version      124
4               DMR_FW_Version      125
5               DMR_HW_Version      A
6               DMR_HW_Version      B
7               DMR_HW_Version      C
8               DMR_HW_Version      D
9               Tuner_FW_Version    123
10              Tuner_FW_Version    125
11              Tuner_FW_Version    128
12              Tuner_HW_Version    A
13              Tuner_HW_Version    B
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.