Are custom content types a good way to model the classic entity relationship model?
In Drupal 7, you'll want the Entity module as it extends the core Entity API to do lots of useful things, and a lot of contributed modules depend on it.

To create relationships between entities (a "node reference" relationship, if you will) you'll want the Entity Reference module. Another more radical approach is the Relation module which provides two-way relationships – this is in early development stages and is a bit more complicated. I would recommend the Entity Reference module, as even though the initial relationship is 1-way, it's simple enough to find the referencing-entity on a referenced-entity when needed.

Regarding your question about tuning the database – the relationships are not setup as foreign keys or anything like that, as Drupal handles your database constraints in code and not within the database schema itself. That's a whole other conversation. The tables that contain fields for a specific node type, including the table that includes a reference to another node ID, are just joined with the node table on "entity_id". There are pros and cons to this – if you're a traditional database admin type, this may seem problematic, but it's the job of the CMS to maintain integrity in this model. I don't think you'll find ways to tune the database via new indexes, although you could certainly try. Thousands of nodes is not a lot of data and you should see Drupal 7 – in a properly tuned environment, with queries and views that do not contain extraneous joins – performs quite speedily. 

My 2 cents.