Den 2011-05-06 16:09 skrev Sergio H. Gonzalez såhär:

> Hello there! I use Firebird 2.1, but this question is not specific to
> FB, I think
>
> Basically, I have a table with a field which can "point" to two tables
> (one or other, but *never* to both)
>
> What the experts says about this scenario?
>
> Option 1) is better to have a field which represent at what table I'm
> pointing and then the ID field... ?
>
> Option 2) or just two ID fields (FK to each table) and just leave null
> the field I not use...

I don't like either option. Seems like a convoluted design.

Thinking in object/UML terms, you have classes Article, PurchaseInvoice
and StockMovement. Both PurchaseInvoice and StockMovement can affect the
stock of several Articles. I don't think PurchaseInvoice "is a" stock
movement or vice versa, nor do I see them both relate to a conceptual
third class in an "is a" manner.

But I do feel that a PurchaseInvoice is not a stock operation in itself,
but rather "results in" a stock operation. So, I'd actually introduce a
third class StockOperation. Each PurchaseInvoice has a single (possibly
optional) link to a StockOperation.

A StockMovement may be a subclass of StockOperation. That would depend
on areas of your domain that I don't know about.

Assuming StockMovement is a subclass of StockOperation you could merge
StockMovement into the StockMovement table. So, this is what we get:

PURCHASE_INVOICES
==============
ID INTEGER,
DESCRPTION VARCHAR(100),
ID_STOCKOPERATION INTEGER
FOREIGN KEY REFERENCES STOCK_OPERATIONS (ID)

STOCK_OPERATIONS
================
ID INTEGER,
DESCRPTION VARCHAR(100)

ARTICLES
========
ID INTEGER,
DESCRPTION VARCHAR(100),
LAST_IN_ID INTEGER
FOREIGN KEY REFERENCES STOCK_OPERATIONS (ID)

I would assume that STOCK_OPERATIONS has a detail table specifying which
articels were affected in what way.

Kjell

> I hope I'm clear with my question!!
>
> here it is a simple case (not a real one) of what I'm asking.
>
> PURCHASE_INVOICES
> ==============
> ID INTEGER,
> DESCRPTION VARCHAR(100)
>
> --
>
> STOCK_MOVEMENTS
> =============
> ID INTEGER,
> DESCRPTION VARCHAR(100)
>
> --
>
> Option 1)
>
> ARTICLES
> ======
> ID INTEGER,
> DESCRPTION VARCHAR(100),
> LAST_IN_TABLE CHAR(1)
> LAST_IN_ID INTEGER
>
> Option 2)
>
> ARTICLES
> ======
> ID INTEGER,
> DESCRPTION VARCHAR(100),
> ID_PURCHASE_INVOICES INTEGER (FK TO TABLE PURCHASE_INVOICES)
> ID_STOCK_MOVEMENTS INTEGER (FK TO TABLE STOCK_MOVEMENTS)
>
> [Non-text portions of this message have been removed]
>
>

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64