Best way to manage a huge database

[perrohunter]

Hello everyone !

I was wondering what do you suggest is the best way to handle a huge database

lets say i have 11202 rows , and that i have 109 posible colums !

that sounds huge !

Now , for example , not all the rows use the field "air" , lets say only 1000 out of 11202 use the field and so on for other kinds of fields

Would it be better if i try something like a multidimensional tabling to retrieve information ?

I mean , i can make a new table and store there which id's from the main table have the field air , and fill the info, ie: air_id , main_table_id , air

so id get a small table that only will talk about 1000 rows and wont be a field thats 10202 times empty on the main database right ?

But imaging i make this for 104 fields out of 109 , If i retrive info from the database from multiple tables to reconstruct an item , that it use to be on the same table , Will the Database traffic get heavier ?

Thanks for any idea 😉 and excuse my english 😃 😃

[jheitz]

Bufff... where to start from....

A table with 109 columns is poor database desing. Funnily enough, you seem to have realized already by your suggestion to use Foreign Keys to store related data in different tables. Basically you are trying to reinvent the wheel here. I recommend you read about Relational Databases and the 4 Normal Forms to understand why what you have is not good and why you should follow the Relational approach.

Just as a taster. Imagine that within your 109 table column you have some MANUFACTURER fields such as MANUFACTURER NAME, MANUFACTURER ADDRESS, MANUFACTURER PHONE NUMBER, and that you have 5,000 records that have the same manufacturer data. With your current approach, what happens if the manufacturer changes address or phone number? you would have to update 5,000 records!

Anyways, this is just an example, do some reading, it will do you good.

[perrohunter]

thanks for the suggestion