I have a system with "main" DB which stores information of vehicles in single table with…
- User
I have a system with "main" DB which stores information of vehicles in single table with vehicle_id AS PK and ist_iid AS FK indicating it owner. There is also another table for sharing vehicles. This data is then synced to other database which keeps it in denormalised form: the PK is (ist_iid, vehicle_id) and the vehicles are duplicated to satisfy sharings. This make it easy to query; however we no longer can treat vehicle_id as unique, even though it is in certain sense.
I’m in the process of refactoring these synced tables and was wonder what I can do about this situation. I could leave it like this, but the PK can be annoying sometimes. On the other hand, the schema from main DB is even worse, as each query would require UNION and knowledge about how sharing works. How would you design schema in synced database? Explain why you choose it.
Assistant
Based on your description, I'll propose an optimized schema design for the synced database that balances query efficiency, normalization, and usability. The key challenge is maintaining vehicle uniqueness while efficiently representing sharing relationships.
Proposed Schema for Synced Database:
CREATE TABLE vehicles ( vehicle_id BIGINT PRIMARY KEY, -- Unique vehicle identifier owner_ist_iid BIGINT NOT NULL, -- Original owner -- Other vehicle attributes (make, model, etc.) -- ... ); CREATE TABLE vehicle_access ( ist_iid BIGINT NOT NULL, -- User with access (owner or sharee) vehicle_id BIGINT NOT NULL, -- Reference to vehicle is_owner BOOLEAN NOT NULL, -- Distinguish owner vs shared access PRIMARY KEY (ist_iid, vehicle_id), FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id) ON DELETE CASCADE ); -- Optimize common access patterns CREATE INDEX idx_access_by_vehicle ON vehicle_access(vehicle_id); CREATE INDEX idx_access_by_user ON vehicle_access(ist_iid);Key Components & Rationale:
-
Normalized Vehicle Storage (
vehiclestable):vehicle_idas true primary key (resolves uniqueness issue)- Stores core vehicle attributes exactly once
- No duplication → updates affect only 1 row
- Includes
owner_ist_iidfor ownership tracking
-
Access Mapping Table (
vehicle_access):- Represents sharing relationships explicitly
(ist_iid, vehicle_id)as composite PKis_ownerflag distinguishes between:- Direct ownership (
is_owner = true) - Shared access (
is_owner = false)
- Direct ownership (
- Single vehicle can have multiple entries (owner + N sharees)
-
Optimized Indexing:
- PK index enables fast user-specific vehicle queries
- Secondary index on
vehicle_idspeeds up reverse lookups - Additional index on
ist_iidcovers common access patterns
Why This Design?
-
Resolves Uniqueness Problem:
vehicle_idis truly unique invehiclestable- No more duplicate vehicle records → simplifies updates/joins
-
Efficient Querying:
-- Get all vehicles accessible to a user (including shared) SELECT v.*, va.is_owner FROM vehicles v JOIN vehicle_access va ON v.vehicle_id = va.vehicle_id WHERE va.ist_iid = :current_user_id; -- Check sharing status for a vehicle SELECT * FROM vehicle_access WHERE vehicle_id = :target_vehicle;- Single join replaces complex unions
- Indexes make user/vehicle lookups fast
-
Storage Efficiency:
- Vehicle attributes stored once (vs duplication in old design)
- Access table only stores IDs + boolean (minimal overhead)
-
Accurate Relationship Tracking:
- Clear distinction between owners and sharees
- No hidden ownership logic in application code
-
Sync-Friendly:
- Changes to vehicle attributes update 1 row
- Sharing changes modify only access records
- DELETE CASCADE automatically cleans up access
Handling Common Operations:
Operation Query Get user's vehicles SELECT ... JOIN vehicle_access ON ... WHERE ist_iid = ?Get vehicle details SELECT * FROM vehicles WHERE vehicle_id = ?Check permissions SELECT is_owner FROM vehicle_access WHERE ist_iid=? AND vehicle_id=?Find all users with access SELECT * FROM vehicle_access WHERE vehicle_id=?Performance Considerations:
- User-centric queries: Covered by PK index on
vehicle_access(ist_iid, vehicle_id) - Vehicle-centric queries: Covered by
idx_access_by_vehicle - Space savings: ~50-90% less storage than denormalized approach (depending on vehicle attribute size)
Migration Strategy:
- Create new tables with schema above
- Backfill from main DB:
-- Vehicles INSERT INTO synced.vehicles (vehicle_id, owner_ist_iid, ...) SELECT vehicle_id, ist_iid, ... FROM main.vehicles; -- Access (owners) INSERT INTO synced.vehicle_access (ist_iid, vehicle_id, is_owner) SELECT ist_iid, vehicle_id, true FROM main.vehicles; -- Access (sharees) INSERT INTO synced.vehicle_access (ist_iid, vehicle_id, is_owner) SELECT share_with_ist_iid, vehicle_id, false FROM main.sharing; - Verify data consistency
- Switch read operations to new schema
- Drop old denormalized table
This design eliminates the confusing composite key while keeping queries simple and efficient. The explicit access model also future-proofs the system for permission enhancements (e.g., read/write flags).
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.