Stuck in database development
I am looking to setup a database for my "test server"/offline access to collect data as I find it so it will be easier to compile the data to put the info online in a more accurate fasion.
I have a basic jist of how to do the database, but I do have a few questions.
Qustion 1) I know for displaying row information you use $row['row_name'], would $table['table_name'] work the same way? Essentially what I am tryinging to do is build the database so that I can output the information in a tabular format...
Question 2) When entering data. How could I mesh the data together for instance on one day I find a code name and a spare part number for a particular board, then two weeks later I find a real name or a retail part number.
Would also like to build an html data entry page instead of using the phpMyAdmin interface..
ok so after a lot of tweaking I got the code redone and it works to the point the the table row data is displayed. I hard coded the table header for now til I figure out how to display table names like the rows..
I had a little trouble with the sorting, was using DESC, but determined ASC was what I wanted for my purpose.. :D
Not I need to figure out how to fill the voids (blank spots in the html table based off the database..
But now the problem is that you have a very bad database design.
There is NO REASON to have one database table per "folder" (whatever a folder means to you) and many, many reasons to only have a single table for *ALL* folders.
So if what you are talking about is that one of your "folders" corresponds to "asus/pegatron", then you should put *ALL* your "folders" into one table and add a column named "folder".
And then you would do, instead of what you show:
Don't start from a bad database design and then hope to compensate for it in your PHP code. Get the DB design right first and then the PHP will be simpler.
Thanks for the reply.
The website this database that I am collecting information for currently contains 16 folders one for each OEM manufacturer (15 known and a folder for unknown/OEM[i.e. unbranded unique design]). with pages for each motherboard in the appropriate folder.
The project is a huge one that has moved into its 13th year and I have come to the conclusion that its time to pool the data collected into a db versus having a bazillion different places to look while building the web pages. Mainly to help aleviate errors in the data.
The original concept of the project was based on desktop boards only but as time wears on we are uncovering retail manufactures for laptop and Workstation/Server boards
So to answer your inquiry, the current setup the tables correspond to the manfuactures:
OEM/Unknown <- dump folder for either boards with only tidbits of info or strict custom design by an unknown manufacturer
Current "design" only have one table "asus/pegatron" so far...
And the ultimate goal is to be able to pool the data together to eventually wind up with this example
Known Codenames: Escape, Explorer, Explorer2, Explorer4 Focus
Processor Info: AMD Athlon XP3400+ (Socket/FSB info as well)
RAM Info: Number/Type of Slots/Sockets, FSB, etc
Chipset Info: Brand/Series info (North and South Brige info)
Type of video options: (i.e Integrated XXXX HD Graphics and PCI x16 Graphics cards)
Type of Audio options: (i.e. Integrated XXXX HD Audio with CODEC: xxxxx)
Expansion Slots: (i.e 2x PCI Express x16 Graphics Slots/4x PCI Express x1 Slots)
Known Spare Part #'s: (essentially the part numbers used to order replacements from the Compaq/HP)
Documentation Availible: (manual, Quick Reference Guide, Maintenance/Service Guide)
Models Found in: (broken down by branding [i.e. Compaq, Compaq/HP, HP]
Confirmed reflash: (Some people have been able to reflash their BIOS back to the retail version)
And with better collected info I can update/build pages with more accuracy.
Website/page disscusion that coincides with this project.
So, again. One table. "allBoards" seems like a good name, but up to you.
And add one field, "manufacturer", which is where you distinguish the board maker.
But now comes the much harder part: It is considered very bad DB design to put a *LIST* of values into a single field. So, just to pick one example, you should not do this:
Do you HAVE to do this? No. But depending on what queries you may later make against the DB, it may prove invaluable. Each field you have there that potentially has a list of values in it needs to be examined to figure out if it should be done as above (known as a "1 to many relationship" or, sometimes, a "lookup table").
Even the fields that only hold a single value might need this treatment. The most likely candidate, as I see it (but you would need to carefully review your data to know for sure) is "CPUInfo". But there well could be others.
A *good* DB design is one that will allow you to later make most any query against it that you can dream up. Just one possible example:
"Find all boards that support an AMD Athlon XP3400+ CPU and that have at least 4
RAM slots and at least 3 PCI Express x1 Slots."
You won't easily be able to support such a query if you have multiple pieces of information in a single DB field.
Gonna work on getting things setup, I may be back with more questions but the insight has at least steered my in the right direction...
CREATE TABLE codeNames(
codeName VARCHAR( 100) ,
CONSTRAINTFOREIGNKEY boardID REFERENCES allBoards( boardID )
) ENGINE INNODB;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES allBoards(boardID)
) ENGINE INNODB' at line 4
So the premise of Auto Increment is the database takes care of numbering, if that is the case why am I being forced to enter a number per entry? shouldn't the first entry start at 1, and each entry after go from there?
Trying to pupulate the allBoards table with info.. And coincidentally even though I created it as allBoards its showin as allboards..
You missed the spacing:
You must either omit the field name of the auto_increment field from your INSERT *or* supply a NULL as the value, else the value you give overrides the auto_increment feature.
If you are on Windows, the case (upper vs. lower) of table names is not significant and by default all are converted to lower case by MySQL. I believe that even on Linux this behavior is optional.
First off, Old Pendant, sorry about not recognizing you in my last post. Reviewing the thread this morning I realized you were the only one to reply..:o
I am still toying with the idea of this project but haven't really had the time to devote to it. Just recently rejoined the workforce.
As far as what has been discussed so far.
When I tried creating the:
CREATE TABLE codeNames (
CONSTRAINT FOREIGN KEY boardID REFERENCES allBoards(boardID)
) ENGINE INNODB;
I am not sure if it is the version of MySQL or not that is causing the hangup, but having the constraint line in the create block as you have it, it produced the error posted above.. The formatting went loopy from mySQL to here, but it was entered as you wrote it.
I did manage to pick up a couple more books covering php/mysql so hopefully I can finally grasp things better.
Missing the *REQUIRED* parentheses! I looked at that a half dozen times and missed it!
Before you get to far down the road it might be best for you to do some reading on database normalization. That will give you a great deal of help in designing your database.
An Introduction to Database Normalization
Also, you may find it easier to design if you use a development tool such as Mysql Workbench. It's free.
|All times are GMT +1. The time now is 08:48 PM.|
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.