Is it worth to split tables bases solely on the business goal?
NOTE: The tables all have identical columns.
One orders table, with a orderType column to differentiate them.
TWO tables called sales_orders and purchase_orders.
How about even more tables like sales_quotes, sales_orders, sales_backorders, purchase_requisitions, purchase_orders...
I believe approach 3 is pushing it too far, while approach 1 is too much mental load to keep filtering 2 very distinct business goals.
Approach 2 seems like just enough separation, but not sure if it will haunt me later.
PS: I am aware it's opinion based, let me have this one.
Is it worth to split tables bases solely on the business goal?
In my opinion and experience, the short answer is yes. Indeed, not only it is worth it, business (functional) requirements are in fact the main if not the only guiding factor, certainly to the conceptual data model. Technical considerations, including plain normalization, should come down the line, and anyway be taken with care: the more the implementation diverges from the conceptual model (even just in terms of levels of abstraction!), the less it becomes understandable and maintainable (and extensible, etc.). ("Avoid premature optimization: already at design level.")
More specifically, for a concrete criterion: consider the life-cycles of your entities (as represented by state-transition diagrams, including the users/roles involved): one and the same life-cycle => same entity, different life-cycles => different entities.
(In general, to entities may be associated a state-transition diagram to represent what informally may be called their "life-cycle", which is the most basic component of what in SE is called (a representation of) the "flow of control". Correspondingly, in the entity one would find a 'State' attribute to keep track of the current state. For example (and just for example, as concrete solutions depend on requirements), an Order might be: Received, TakenChargeOf, Rejected, Cancelled, Shipping, Fulfilled, Payed, Refunded, Backshipped.)