large database

[hatim]

Hi

I have a database with about 3.5 million unique items. There is a table that covers which categories each item belongs to. The average item belongs to 3 categories.....the categories are used for navigation on a website.

The question - is it better to split each entry for each category into a its own row (~10m rows) or to have each unique item and list all of it's categories in a delimited fashion (category2323|category23489|category382233) in a single row for each category and only have around 3.5m rows.

Please help me out.
Many thanks -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[retro]

that depends on your queries

if you ever need to search on items belonging to a specific category, then the delimited strategy requires a table scan, and trust me, you never want to do that on 3 million rows

with separate rows for each category, you can use an index, and that works in fractions of a second

does that answer your question? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[yoyo]

No offence and no pun intended, but a programmer who recommends storing multiple values in a delimited field obviously doesn't know the difference between an index-based query and a table scan. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[hatim]

The programmer had told me he had never worked with a database this large, but he's primarly a php guy. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[hotty]

Then my suggestion is to hint to him that he needs to read up on database design, normalization, and optimization. Otherwise, you could end up with something that is ineffecient. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[pixel]

one more option, he can also hire a separate person to do the database design.

Good Luck -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[hatim]

yes, u are right,

I was just trying to get the structure down, I will hire somebody else down the road to go in and try to optimize the database and queries. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Garros]

I would not recommend this, u need to get the database structure orrect the first time. it's not just as easy as "optimize the database and queries". resturcturing the database to correct fundamental flaws often results in have to rewrite major portions of the application.

Bye -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --