well, i'll throw my two cents in, and you can evaluate them for yourself

i can only tell you my experiences and the way we do things at my shop; you can review them and see what you like, since there's no real rules to follow:

for prefixes, our database supports multiple applications that share a common database, so tables related to a specific application have a matching prefix; cmsBusinesses, cmsContacts, etc for a contact management system for example. in your example, we might have a table named appTransactions and appTranDetails for example, instead of a prefix to imply a master-detail relationship;

1. no underscores allowed in table names; CamelCase is preferred, and the table name must imply the content/business purpose of the table.

2. every table has an identity as the first column. the column name is always the table name + 'ID' or 'TBLKEY' or something so it's obvious it's the key. cityid for tbCity, stateid for tbState, etc. we leave identities starting at the default of 1,1 unless a business reason needs it otherwise.

3. foreign keys in child tables have the column name the same as the key it is pointing to; so an cmsAddress table has a column named cityID pointing to tbCity(cityID); in the cases where we need more than one, the column name must still be a part of the column name, adn just add a prefix: percityId and buscityID for collections of addresses for personal vs business; helps later so you can confirm that every column ending in "ID" is either a PK or FK, for example.

4. lookup/shared reference tables start with tb; tbCity,tbState,tbCounty; "TB" tables are shared lookups. if a table of say statuses is needed (Open,Closed,Pending) whatever, that table would be for a specific application, so cmsStatus and appStatus might start out with the same values, but from experience, we found that eventually one app needs to change a shared table.

appCity for example, might be a subset of all cities, as not city is really needed.

5. try to avoid abbreviations if possible; in your example would a new developer intuitively understand that tm_,tr_ and td_ relationship?

or would something like AppMaster,AppMasterTransactions, and AppMasterDetails be more descriptive and intuitive?

same thing in column names...silly things like lglstatus vs LegalStatus and other abbreviations; made sense to the person who coded it at the time, but if you step back or someone else looks at it, would they know?

6. no Hungarian notation for data types, only their purpose; a perfect example is "amount" columns; we might name a column InvoiceAmt, and it's decimal or money for the data type, but some business processes capture whole dollars;sAddress, iInvoiceAmt and dInvoiceAmt might imply the data type, but that might need to change in the future; there is lots of info here about Hungarian Notation and it's pitfalls:

http://www.sqlservercentral.com/search/?q=hungarian+notation

now that i said what we strive for, let me also say this: there are exceptions to every rule above; some tables don't get an identity, sometimes a table gets an underscore to match a different business process, etc. but they change because there was a reason, and not for an arbitrary reason.

I hope others chime in and offer some more examples and guidelines they use as well.

Lowell