1,105,633 Community Members

Create a Database with multiple serial numbers

Member Avatar
brandon66
Junior Poster in Training
77 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

Member Avatar
brandon66
Junior Poster in Training
77 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
SirMahlon
Light Poster
40 posts since May 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
brandon66
Junior Poster in Training
77 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 
product {pid, pname}
productattribute{pid,aid}
attribute{aid, aname, value}

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

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

Member Avatar
brandon66
Junior Poster in Training
77 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

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.

Member Avatar
brandon66
Junior Poster in Training
77 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article