[RESOLVED] one big table or several small ones ?

VisionsOfCody

Hi just a quick question : I'm starting a site that will have several mini-stores in it all based on the same model and using the same structure with a grouped ordering system etc

would it be better to have all the stores' products in one db table - about max 500 products in total - with each product having a store ID and a category ID or would it be better to have a separate db table for each store ??

It seems that having 1 big table would be better in case I need to change the system and that would avoid having to change all the tables for all the stores, but apart from that I can't think of any reasons to choose one way or the other - is there an issue i haven't thought of ?

thanks for your advice

nemonoman

Big.

Sxooter

I'd put each one in it's own schema so that no two companies have to worry about their data showing up on each other's site. the closest thing to a schema in MySQL is separate databases, so I'd use those.

While it might be easier to maintain the schema of your setup with one big table, it's not the best option from the point of view of keeping your customers separate from each other.

Update your schemas with .sql scripts, and you can automate if pretty easily by just running that script against all the dbs / schemas in your server.

Bascially, things that are related should be stored together. These data sets aren't really related, I take it.

Sxooter

OTOH, if those data are related, and you'll be letting people order from >1 store at a time, then one big table.

VisionsOfCody

well it's a kind of wierd set-up - there's one site with 5 mini-stores in it that are all built the same way - the user can hop between the stores and order stuff in any of them but there's just one shared cart and they pay for all their stuff at the same time (and there's one grouped delivery for all 5 stores)

so in fact it's kind of like 1 big store with 5 different 'floors' each run by a different shopkeeper - they don't seem to mind the fact that they'll all be able to see how much businesss the others are doing (from the delivery sheets)

so I suppose I'll go with the 1 big table

thanks for your answers

Sxooter

Yeah. The reason to separate or not really lies on whether they're truly independent. If they were truly independent, where you might host them on different vhosts or something, then separating them would make it easier to split them out for load purposes, and run individual reports for each store. OTOH, if they'll never be separated from each other, then they're probably related after all in some way.