Unless you need complex many-to-many relationships, you should stick with the built in tables where you can.
In your comment above in reply to Toscho, you essentially describe a custom post type with attachments (images), meta-data, and a taxonomy (tags). All of this would be a snap to accomplish using the provided API, so I don’t see what could be gained in that particular example by using your own tables.
If you ultimately do want to define your own tables, I suggest storing comma-separated values instead as individual rows in a separate table and doing JOINs, querying on that data would otherwise not be so easy.