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?
Products
DMR
dmr id
ISN
dmr firmware version
dmr hardware version
battery serial number
pcb firmware version
pcb hardware version
Tuner
tuner id
serial number
firmware version
hardware version
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}
productattribute{pid,aid}
attribute{aid, aname, value}
would work.
This enables any product to have any number of attributes.
OR
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.
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?
Products
Product_ID Product_Name Product_SN
1 DMR 1.12235E+11
2 Tuner 12345
3 DMR 1.12235E+11
4 tuner 123456
Product_Attribute
Attribute_ID Product_ID
1 3
2 1
10 2
12 2
5 3
8 1
Attribute
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.