Is this a good way to store data in my database?

[5jgibbs]

I am making printer queries via SNMP and trying to store them in a database. The database has a table for each printer. Each table for each printer stores

+------+-------------+------------+---------------+-----------------+-------------+
| id | toner_level | pages_left | total_printed | fuser_countdown | fuser_total |
+------+-------------+------------+---------------+-----------------+-------------+
| 7088 | 26 | 7216 | 164954 | 60105 | 225000 |
| 7089 | 26 | 7216 | 164954 | 60105 | 225000 |
| 7090 | 26 | 7216 | 164954 | 60105 | 225000 |




Now, someone told me it would be better if I stored each of these things in separate tables? And that I am storing to much in one table? Can anyone guide me here as I am not a database programmer.

Thanks

 

[ingresman]

If that's all the data you storing I would think that would be ok.
What I would suggest it not having 1 table per printer but add a printer column to the table and identify the printer by that. If you have a table per printer you would have to create a new table everytime you got a new printer.
It might be worth adding a date and time stamp so you could sort the records and see how each printer is doing. E.g. if you wanted to see how printer LJ1006 was doing you could query like
select * from printers where printeName = "LJ1006" order by timeInserted. O something, it really depends on what your requirments are.

 

[vacunita]

Each field in a different table? whoever told you that has no idea how to create a relational database.

Heck you could probably store the information for all the printers in the same table If they all have the same columns.

Of course your sample seems to have many repeating values, the onyl difference there is your Id.

I would assume as print job occur the toner level, the pages_left number and the fuser countdown should decrease.

I'm guessing ID refers to the print job, and the printer status after it has completed the job.






----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[5jgibbs]

Well the ID was so I could order the data from first to last. This is just a cron job that happens every 5 min. Then it grabs the info from all the printers and does the following. This is just so we can keep tabs on all the printing, toner levels, ect...

The fuser_countdown is actually subtracted from fuser_total to get the remaining pages left. Hm, anyone have a good tutorial or book on relational databases.

Thanks for the suggestions.

 

[vacunita]

I would then agree with ingresman in adding a couple columns to your table.

Mainly a date/time column so you know when it was the row got added, and yes if you want to keep all printers in a table an additional Printer_id column to control that.

you could if you wanted to have an additional table that controls your printers. Printer_id, printer_name, description, location if relevant etc..

Then when you need to add a new printer, you can add it to the printers table give it an Id, and use that ID to log the status in your status log table.











----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[5jgibbs]

Here is another questions, if I have a table that is just filled with say, a printers name such as

printer1

And that corilates with the IP

192.168.1.12

How will printer1 be linked to 192.168.1.12

such as printer1 --> 192.168.1.12

Thanks for all the help!

 

[vacunita]

Technically the IP would be included as a field in the tablñe with the printers name.

Or you could conceivably have a table with the IP adress and an ID field that matches an ID field in the table with the printers name.

However unless a printer can have more than one IP address, and an IP address can be assinged to more than one printer, the IP should be included in the same table that has the Printer name and other information pertaining to the printer itself.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ingresman]

What vacunita is suggesting makes sense and is known as denormalisation.
In a data model you would have a seperate table that had the printer name and the ip address (and an id field) so you would only hold printer information in one table in one row. You would refer to that printer usually by a surogate key such as an incrementing ID in a mysql table. The advatages of doing this are you could change the IP address of the printer in one place should you need to, the disadvantage is you have to join the data table and this reference table together whenever you run a query. This is not too bad in your solution but if you were getting 1000's of hits per second it would be slow and you might denomalise it.
Data base design is a science and there are some mechanical steps you can go through such as first,second,third (and quite rarely) fourth normal form.
To read up try something like

http://www.amazon.co.uk/Database-De...=sr_1_1?ie=UTF8&s=books&qid=1240391450&sr=8-1

I've not read this so can't vouch for it. Have a look at books on amazon and check the user reviews, or try for some primers on the web.

 

[johnwm]

There is also an excellent introductory article on relational database design at

http://r937.com/relational.html

You may also like to read faq222-2244 as I notice that you've asked over 30 questions on these forums, but only marked one of the answers as being valuable.

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller