Is It Worthwhile To Have a Table That Only References Columns From Parent Table-CodePudding

I currently have a database for trading cards and the main table is AllCards. This table has twenty columns and has over millions of rows, but my question regards towards the column boolean type isSold in AllCards. Whenever a user loads our site, a query like “select someColumns from AllCards where isSold = False” is done to show all the available cards. However, I was wondering if it would be better to have a separate table labelled historic and current to not query millions of rows. My backend code will place cards that are not sold yet to Current and once sold, delete that row in Current and move that row to Historic.

Table Historic
Uid INT,
HCardID INT,
FOREIGN KEY Uid REFERENCES User(id),
FOREIGN KEY HCardID REFERENCES AllCards(cardID)

Table Current
Uid INT,
CCardID INT,
FOREIGN KEY Uid REFERENCES User(id),
FOREIGN KEY CCardID REFERENCES AllCards(cardID)

As shown above, Historic contains IDs to AllCards that have been sold and Current contains IDs to AllCards that have not been sold yet. Thus, when a user loads our site, I can just do a join query on table Current and iterate table AllCards where CardID = CCardID, instead of having to query through all rows of table All Events and check if it was sold or not. Is this a valid approach for performance and organization, or should I just forget about it, and only have an AllCards table and use the columns isSold? On the same topic, I also have implemented a table Week, a table with all the cards bought this week from a user. For this table I also do the approach of

Table Week
Uid INT,
WCardID INT,
FOREIGN KEY Uid REFERENCES User(id),
FOREIGN KEY WCardID REFERENCES AllCards(cardID)

This way, I do not have to query millions of rows to find cards that the user has bought this week and instead can do a join from Table Week and AllCards where cardID = WCardID and Uid = Uid. Does this help performance and organization and is this approach even worthwhile? Or should I just do go through rows in AllCards and do a query to get the same data for cards bough this week by the user?

CodePudding user response：

The week table should not exist. Instead, have a datetime in AllCards and index it.

It is probably slower to go back and forth between Current and AllCards than to simply scan the entire AllCards. Ditto for History. Anyway, both are equivalent to having a pair of indexes on the big table. I assume there is some column that distinguishes a "current" entity from a "history" entry in AllCards?

If the same "card" shows up again each time it is traded, then you might need a table of Cards (with 'card'-related columns) and a table of Trades (with trade related columns. Trades would probably have UId and CCardId columns and the TradeDate, but Cards would not. TradeDate would definitely be indexed, so you can get the week's trades efficiently.