Is it a good practice to use pickled data instead of additional tables?

Many times while creating database structure, I get stuck at the question, what would be more effective, storing data in pickled format in a column in the same table or create additional table and then use JOIN. Which path should be followed, any advice ?

For example: There is a table of Customers, containing fields like Name, Address

Now for managing Orders (each customer can have many), you can either create an Order table or开发者_如何学编程 store the orders in a serialized format in a separate column in the Customers table only.

It's usually better to create seperate tables. If you go with pickling and later find you want to query the data in a different way, it could be difficult.

See Database normalization.

Usually it's best to keep your data normalized (i.e. create more tables). Storing data 'pickled' as you say, is acceptable, when you don't need to perform relational operations on them.

Mixing SQL databases and pickling seems to ask for trouble. I'd go with either sticking all data in the SQL databases or using only pickling, in the form of the ZODB, which is a Python only OO database that is pretty damn awesome.

Mixing makes case sometimes, but is usually just more trouble than it's worth.

I agree with Mchi, there is no problem storing "pickled" data if you don't need to search or do relational type operations.

Denormalisation is also an important tool that can scale up database performance when applied correctly.

It's probably a better idea to use JSON instead of pickles. It only uses a little more space, and makes it possible to use the database from languages other than Python

I agree with @Lennart Regebro. You should probably see whether you need a Relational DB or an OODB. If RDBMS is your choice, I would suggest you stick with more tables. IMHO, pickling may have issues with scalability. If thats what you want, you should look at ZODB. It is pretty good and supports caching etc for better performance