On the other hand, having all fields in all tables means you do not have to do a join, which means you can have a richer and

more complete overview of something directly on your main screen without affecting the performance too much, especially if you have

some form of caching enabled.

The downside of this is that you really need caching especially if we are talking about blobs whose size is, so to speak, "accomplished".

I am not anticipating there to be many blobs and they are only in a few of the tables. It's difficult to explain the exact use but imagine a document that is mostly text and a few illustrations like an academic paper or similar.

There is one exception though and that will consist almost completely of blobs (actually PDF files).

Also, speaking about blobs: I do believe that - really - you should avoid putting them inside the tables as much as possible.

Interesting you should say that, but I also heard of users with terrabytes of blobs in tables? I am expecting 500 Mb - 1Gb at most.

Another alternative (and one I would highly endorse in this day and age) would be to be smart and store the blobs into the

users' dropbox accounts and then use an URL reference to pick it up, having such reference stored in the DB).

Yes, this would be an option but I would also to like the option to be able to read the documents 'offline' using the embedded server on a laptop for example.

There will only be a handful of users (50 or so - it's an internal project) so the scale is relatively small. Sorry I should have explained that in my original email.

The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP.

No, Just no. Each table its own trigger. Do not do that. Trust me, that's a *REALLY* bad idea. If anything, use numeric constants that cannot be hijacked as easily.

Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)?

Yes, you screw up the security. Doing that means you would need to use Dynamic SQL and that's 99% evil. Do not do that unless you are really obliged to for some reason.

Thank you, point taken!!

It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application.

In my opinion, the only way to do that is to have them backed up on a different - inaccessible - database. This would also open up a business opportunity if your

product is commercial, i.e, "be able to retrieve the last audit details if you can't guarantee that the db has not been fiddled with" (such as with an intrusion of some

sort or suspicious internal activity).

This is an interesting idea. I thought about having the 'audit' table in a different database and inserting a table_id, record_id, action, date and time from the trigger. However reflecting on your earlier comments this probably isn't a good idea because I think it would require an EXECUTE STATEMENT :(.

Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation?

UDFs are not an obfuscation solution. Consider the following scenario: you use an open source UDF for encryption.

Now, a malicious user turns off the FB service and substitute your legittimate version of the UDF with one which also

contains a keyboard hook or worse, a global hook registering all calls made on a Windows server. Now you are in serious trouble.

I am hoping my users are that that malicious, but it is a good point. I wouldn't want it to end up as a back door in case the database was ever deployed somewhere that I haven't really considered.

Does it help?

YES!! Thank you!