I am building a web application which manages an email server and I'm in the process of designing the database structure that will be used however I'm stuck on a design decision.
My problem is as follows : At delivery time the mail server needs to look up mail routes. There are inbound routes and outbound routes which essentially are the same thing however and depending on the direction of the mail one or the other will be used.
From an object oriented design I have a top level table containing a matcher field to match on either the source or destination domain and a OneToMany relationship to the mail routes table containing the routes.
Mail Routes (long route_id, String gateway, int port, @ManyToOne long domain_id)
I see I have 2 options :
1 ) I create a separate model one for inbound routes and one for outbound routes creating 2 @OneToMany relationships in the domain_routes table. This will make the routes easier to manage and clearer within the code however will require a new table.
2 ) I add a type field which can be used to filter the route type. This will remove the need for a separate table but it seems more messy.
Thank you for your help in advance.
subject: Hibernate database design multiple tables per class or add a type field