GOOD TABLE STRUCTURE?

[lazytrucker]

Quick question to see if any one can point me to some articles or something.

I am redesigning some of the larger tables in my "mysql" database, I want to create a new table with somewhere between 25-30 columns and 1000's of rows. This will be getting queryed alot and query caching is not an option how will this effect the response time of my application and is it better to have more rows or more columns in a table structure as I could redesign either way??

Cheers Lazytrucker

 

[r937]

which is better, more rows or more columns?

depends on the table

in general, more rows is better, because more columns might violate first normal form

rudy

http://r937.com/

SQL Consulting

 

[sostek]

I tend to agree with r937. If you've got 25 to 30 columns in your table you've not yet figured out all your dependencies and such.

That is the beauty of a relational dabatase. Everything doesn't have to be in the same table. That's where keys come in - primary keys in the home table, and foreign (related) keys in other tables.

Best example I can think of is a basic POS system I put together. There was a customer table the held relevant customer informatin such as name, address, city, state, zip, phone with a key field called cust_id which was simply an autonumber.

But a customer could make purchases - so the orders table was defined as having order_id as it's primary key, cust_id as the foreign key, and then the order date, who wrote the order, etc.

Order lines had a line_id as primary key, with order_id as it's foreign key and then the item, price, etc.

The credit table worked the exact same way with cust_id being the foreign key.

 

[ericbrunson]

If you have 40 attributes for every record and they're always filled and none of them duplicate data, then you need 40 fields.

If you find yourself creating a lot of fields that are only sometimes filled, i.e. a very sparse record, then you may want to consider a more normalized form of the data. Put the list of attributes in a table and make an xref table that has the id of the attribute, the id of the record it relates to and the value.

 

[lazytrucker]

I have looked at primary and foreign keys, and it is NOT an option, normalization is not possible for my application.
I have heard MYSQL can handle (Supposedly) over 1,000 columns and over 1,000,000 rows of data what I need to know is what is the performance issues related to having more columns or more rows.
Would breaking the table into sub tables be a good idea related to by ID

Cheers LazyTrucker