Hey everyone! I'm currently designing an advertising product system for a commercial directory API using NestJS and TypeORM for a city business app. The system allows users to view business cards and offers ad placements for businesses.
Initially, I only had banner advertisements, but the client now wants to include four different types of ad products through the same interface. Here are the product types and their unique attributes:
- **Banner**: `imageUrl`, `section`, `categories[]`, `startDate`, `endDate`
- **Popup**: `imageUrl`, `redirectUrl`, `categories[]`, `startDate`, `endDate`
- **PowerUp** (premium profile): `expirationDate`, `additionalBranches`, `additionalProducts`, `durationMonths`
- **Badge** (verification mark): `expirationDate`, `badgeType`
They all share common attributes: `companyId`, `paymentId`, `type`, `isActive`, `createdAt`, and `updatedAt`. Additionally, they all need to stay connected to the Company and Payment entity.
I'm considering two design options:
**1. Table Per Type (TPT)**: This involves creating separate tables for each type of ad product that inherits from a base entity.
- **Pros**: Clean separation of types, better type safety, and no null fields.
- **Cons**: More tables to manage, complex joins, and multiple relationships required in Company and Payment entities.
**2. Single Table with JSON metadata**: All data is stored in a single table along with a JSON column for type-specific data.
- **Pros**: Simpler relationships, one table, and flexible design.
- **Cons**: Less type safety, queries can get complex with JSON, and lead to validation headaches.
My main concern is that with TPT, my `Company` and `Payment` entities would need multiple relationships to ad products, which feels off and might go against DRY principles.
I have a few questions:
1. Which approach do you think is better and why?
2. How do you manage the multiple relationships issue with TPT?
3. Are there any alternative design patterns I should consider?
4. How do you balance type safety against simplicity in scenarios like this?
Thanks for your insights!
2 Answers
You might want to consider a polymorphic relationship where you maintain common fields in one table, and each product type has its own separate table. This can help manage relationships more neatly, and you could use fields like `target_type` and `target_id` to pull in from the correct table.
You could also simplify it by using a key-value structure instead of a single row for each product attribute. Just create an attribute column and a value column in a single table. For instance, you could have rows like: `Product, attrib, value`, which makes it easier to add or adjust without complex JSON serialization.
That's a downside though; without defined fields, you lose type safety and won't search efficiently, which adds overhead later.