I need your strategic advise about DB architecture, please
Could you please give me your advise. My DB is built around two main things: orders and invoices. In my orders I use portal row for specification and many-many other add-ons (you can see it on my relationship screenshot). My invoice is 70% of my order with same structure and parametrs. Simple, I want a button MAKE INVOICE on my ORDERS layout and everything will be copied, but I might change my invoice slighlty. First, I made second table for my INVOICES and now I think, that I somehow can use single table for both just with different indexes in key fields like ORD0001 or INV0001. I would use duplitcate (very userful: http://forums.filemaker.com/posts/ac6afe2913), but how can I avoid messing them in one place? Using another layout is one thing, but when user will go thru next/prev records - they will panic..
I'm sure a lot of people here faced the same issue. What is the golden rule for this kind of things? Thank you beforehand.