Experimenting with xPDO, table relations, maps, etc..

Hello,

I don’t have much experience on xPDO and how it works, so I thought I’d do a little experiment application with it. What I’d like to do is an auction application.

There would be one table that holds the auction products. When a new product is created (eg. row added to the products db table), the system will create a new database table to hold the item’s bids. Each bid is bind to certain product and user id.

It would be quite simple to do if the bids would be in a single database table and I would already have certain database structure. In that case I could use xPDO’s reverse/forward engineering tools to build the map files etc. But now when the database is "changing", I’m guessing that I’d need to programmatically generate the map files on the go somehow..

The reason why I’m creating a new bid table for each product is that each product could have tens of thousands bids per product and the table size for single bid table could reach very big sizes when there are thousands of products. Well, there is of course still lots of options to create "fixed" database structure, by doing bid tables for certain product categories etc, but let’s say that I just want it this way. And another reason is that I will found out how the maps work and how xPDO handles such things.

So, first questions that come to my mind are:
- How the relations should be built in xPDO in this case? If for example product table contains a product with ID 233 has it’s bids in a table named bid_233?
- How the map files should be created, what’s the structure in them?
- How the map files are used?
- What else I should know and where to start?

I don’t know if I’m thinking this whole thing some how backwards or wrong, but I couldn’t find information to this from xpdo.org.

Explain to me again why there needs to be a separate sql table for each bid?

I was expecting to get such reply..

Well, to be truthful, I’m not sure where I was going with it.. But.. what the limits are for how many rows are reasonable to have in a single mysql table? There could be 5-10 million rows (eg. bids) in the single table in a month vs 100 tables (one for each product) with 50000-100000 rows per table.. I don’t know what’s best in long run.. but I do know that it sounds stupid and wrong to do a new table for each row in the products table and create relationships between those.. And also, those calculations would not be possible in real life..

But in any case.. would it be possible to do that? or what do you suggest?

A MyISAM table with MySQL breaks on default settings at about 5 million rows. You can change this in the CREATE TABLE clause with MAX_ROWS...but...

An InnoDB table (preferred here) doesn’t have that problem at all.

I suggest creating a couple tables with some foreign keys.

table_product
id
name

table_bids
id
product - FK
user
amount


Then just grab them with:



or the xPDO code:



(You could use getObjectGraph as well, but that code is that way for simplicity.)

Tables are meant to have tons of records. You could always archive, as well, if you’re that worried about it.

Yep.. I know that it can be done like that.. And that I know how to do..

But still.. would it be possible to make those separate tables for each product and relationships between?

Creating a new table for every bid is insane.

When you’re dealing with huge amounts of data, you should sketch out some sort of archive system.

What I mean is: Run a maintenance query periodically (e.g. via cron) to fetch the oldest bids from the activeBids table and move these records to an archivedBids table. I’m not sure if your users will be able to search in old auctions, but it’s sure that mutations will not be necessary / possible anymore. And hence, also much less used than the active-bids table. This could help keeping the active bids table at a reasonable size.

But if we’re really talking about a high-volume site (several terabytes), you will also have to deal with questions about an efficient backup-system, memory, table locks, hardware (RAID?), Apache, mySQL and PHP finetuning. PHP will crap out when tens of thousands of users will try to login/bid/search/pay simultaneously. Sessions will magically disappear, people will wait forever at each request, etc. There is special literature out there for exactly such scenarios.

Yeah.. I understand. I was just thinking that would it be technically possible, but I got it that it’s just plain wrong.. I’ll go with the single table and see what I come up with.. Thanks for the replies.

doze:

What you want to do is possible in xPDO. Check out xPDOManager, a utility class for creating/removing tables and databases dynamically. 097 actually uses it extensively in it’s setup part, and 097 3rd-party components will be able to create their own db tables to manage their own data.

That all said, it’s not recommended to do it the way you described, since it’s poor application design to create/remove tables on the fly because of the overhead caused and confusion with FK definitions.