Three points:
First, you need to use JOIN to good advantage, so as to retrieve the joined data that you want with one trip to the database, instead of repetitive trips to the database. As James Anderson correctly pointed out, this is what relational databases are all about.
However, if you are just learning how to use a database in SQL, then using a database of your own design may take you down the wrong trail. The database needs to be designed well to exploit the full power of JOIN, and of two other relational operators, known as restrict and project in relational math. The SQL buzzwords are WHERE, DISTINCT, and GROUP BY.
Second, the relationship between packages and products is your basic bill-of-materials case. This case has been extensively studied in practice over the last forty years. There's no need for you to reinvent the wheel. However, if you add the possibility that packages can be composed of other packages, then the BOM problem becomes a recursive one. SQL is not built for recursion, but there are workarounds.
If you come up to speed on the nested-set technique for designing containment hierarchies, like BOM, then you reduce what would have been a recursive query into a simple SQL query that the database server can decompose into a simple reiterative process for you. Nested-set is not ultimately complicated, but there is a learning curve here.
Third, the relationship between order details and products or packages is not really a ternary relationship, in the way that term is usually used. Each order detail references an order (the parent), and either a product or a package. This either-or situation is your typical class-subclass (type-subtype) situation, rather than a true ternary relationship. Unfortunately, vanilla SQL is not good at inheritance. You could use extensions to SQL peculiar to your DBMS, if it has them, or you could use one of two techniques, called "single table inheritance" or "class table inheritance". I like Fowler's treatment of this subject.
There's plenty to learn here. You sound like a smart programmer, so you should be able to learn it fast. Just don't assume you already know it. Maybe you do, maybe you don't.
You would store an object in a database record when you need the benefits that doing so provides. There are valid use cases for that, otherwise there would be no such thing as object databases or object-relational databases.
When would you store objects in a relational database?
When your data is schema-less; that is, the objects can contain arbitrary fields. Storing an object means you can put whatever you want to in that object
When you want to store an object graph of any kind.
When the search and join capabilities of a relational database are of secondary importance to the flexibility of storing objects.
SQL Server allows you to store serialized objects in JSON or XML format, and then run first-class SQL queries on them. They claim that you would use these capabilities when:
- Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.
- Your data represents containment hierarchy, instead of references among entities, and may be recursive.
- You want to query into the data or update parts of it, based on its structure.
Best Answer
You would have to know the dataflows between the tables to be able to see how the DB model performs. Once you have that you can calculate the change in performance for a given denormalization (e.g if you decide to duplicate data)
Some rough estimates can be deduced by how many new indexes you would need after the denormalization steps. Each new index must be updated and queried separately which will incur a performance hit proprtional to the number of new indexes.
Big blobs of binary data should in any case be stored in a separate table and not copied around. They are (usually) not queried but returned as part of the final result set after a query against some other set of tables.