pb648174 (google@webpaul.net) writes:
> Is it all that unreasonable to want to add a different type of item
> without having to rewrite all the stored procedures and modify all the
> application logic? Adding a column is a big deal, not because of the
> SQL statement to add the column, but because of all the consequences.
> This is a fairly simple example, I have some much more complicated
> ones, with a table referencing itself and one with 8 or 9 types
> (instead of 2).

You will have to understand that neither I nor Hugo has very poor knowledge
about your business domain. All we know are the names of the tables, and
it does not tell me that much. What we can to is to build from our own
experiences. In the database I work with, there are quite a few cases of
mutually exclusive columns. The number is almost always two, although I
recall that somewhere the number is three. Given what you told us, this
appeared to be the correct solution.

Now you say that you have 8-9 different tables you can refer to. In this
case, I doubt that it is a good idea to have 8-9 different columns with
a constraint specifying that exactly one must be non-NULL. I'm tempted
to say that there might be reason to review the entire data model. And
surely some variation of the suprertype concept is more apt here.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On 5 Mar 2005 16:57:39 -0800, pb648174 wrote:

> Adding a column is a big deal, not because of the
>SQL statement to add the column, but because of all the consequences.

Hi PB,

Could you elaborate on this? I've been trying to think what consequences
you refer to, but I don't see them. I might be missing the obvious, of
course - but there's also the possibility that there's a very simple
solution to what you perceive as a problem. If you post it, others can
try to help you!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> Could you elaborate on this? I've been trying to think what consequences
> you refer to, but I don't see them. I might be missing the obvious, of
> course - but there's also the possibility that there's a very simple
> solution to what you perceive as a problem. If you post it, others can
> try to help you!

Of course, you don't add a column to a table only to have it sit
there, but there will be some code that will have to be added to
retrieve, display, and update the new column.

If you use SELECT *. INSERT without no column lists, and other bad
programming constructs, the impact can be even harder.

So, if it really is the case that tomorrow a third table can be
targeted, then this solution is not a good one. But I also think
that there is something with the data model that needs fixing in
this case.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Ok, forget about the example I posted and consider how you would model
this then: You have an audit log which logs the exact same information
for every single module in an application. In addition to viewing the
log, one requirement is that users be able to click on "details" for
each audit log line item and go directly to the module the audit log
involves.

So obviously there needs to be a relationship between the audit log
line item and all the modules in the system(20-30). Furthermore, new
modules are introduced every quarter, so it should be relatively easy
to add new modules to the auditing system without having to touch the
existing code (so it doesn't have to be re-tested).

How would you model it? And better yet, how would you do the foreign
keys?

pb648174 (google@webpaul.net) writes:
> Ok, forget about the example I posted and consider how you would model
> this then: You have an audit log which logs the exact same information
> for every single module in an application. In addition to viewing the
> log, one requirement is that users be able to click on "details" for
> each audit log line item and go directly to the module the audit log
> involves.
>
> So obviously there needs to be a relationship between the audit log
> line item and all the modules in the system(20-30). Furthermore, new
> modules are introduced every quarter, so it should be relatively easy
> to add new modules to the auditing system without having to touch the
> existing code (so it doesn't have to be re-tested).
>
> How would you model it? And better yet, how would you do the foreign
> keys?

The hour is late, and the descrption is brief, so I'll be brief too.

1) For an audit log, I could consider scrapping referential integrity. Or
have some table that is specific to the audit look to server as lookup
for id:s of the modules.

2) If DRI to the audited rows is absolutely desired, I would consider of
adding the table "auditable_items" that would hold all items that are
common to audited tables. All auditied tables would refer to that
table by the trick that I demonstrated earlier. (And of which credits
for the idea goes to Joe Celko.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Well it's refreshing to hear the same idea suggested that I used when
creating it... Especially after all this talk of bad design.

I am a bit unclear on what you mean by this though:
"Or
have some table that is specific to the audit look to server as
lookup
for id:s of the modules."

pb648174 (google@webpaul.net) writes:
> Well it's refreshing to hear the same idea suggested that I used when
> creating it... Especially after all this talk of bad design.
>
> I am a bit unclear on what you mean by this though:
> "Or have some table that is specific to the audit look to server as
> lookup for id:s of the modules."

One hardly blame you for not getting sense in this gibberish. I did say
that it was late at night, didn't I? :-)

I think that what I meant was that rather having an audit table that
looks like:

CREATE TABLE auditlog (modulename varchar(40) NOT NULL,
....

You would have

CREATE TABLE auditlog (moduleid int NOT NULL,
....

And then moduleid would be an FK to the modules table, which probably
should be specific for the audting. The main advantage is that you gain
some size with the log table.

Actually, in our system we have one general log table where you can
log about everything in datachanges. This table is very simple. We
have (tablename, keyvalue1, keyvalue2) to identify the changed row,
and "colname" to identify the column. There is no referential integrity
at all from the log table.

But you may have more heavy-duty requirements than we have.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp