In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated and has some performance issues (I'm working on that...). My idea is to store statistics in a separate "Profile Statistics" table with the same PK and with the PK as foreign key pointing to "Profiles" in a 0:1 relationship. The linking field is already indexed on the referenced table. DDL:
CREATE TABLE profile ( user_id VARCHAR(50) PRIMARY KEY, attr1.... attr2.... attr3.... ) CREATE TABLE profile_statistics ( user_id VARCHAR(50) PRIMARY KEY, stat1... stat2... stat3... CONSTRAINT FK_user_id FOREIGN KEY (user_id) REFERENCES profile(user_id) )
My hope is that separating these heavily updated (but rarely retrieved) statistics into a separate table is that updates on them will be much faster and will avoid increasing bloat on the main table, which is already heavily updated via API. Is this a common approach, or is this a bad idea? The alternative is to just have the statistics remain in the main table, which would prevent the creation of a new table w/ index.