--- In firebird-support@yahoogroups.com, Adriano <fadrianoc@...>
wrote:

>
> Hi Adam,
>
> It seems like a strange requirement, that information contained

solely

> > in TableA is enough to correctly populate TableB. Are you sure

TableB

> > should not simply be a view?
> >
>
> My exact scenario is:
> Table A (contains income money transactions)
> FIeldID Field1 Field2 Field3
>
> Table B (contain contability, income money transactions + others

income

> money transactions - outcome money transactions)
> FieldID Field1 Field3 FieldID_TableA
> Field1 and Field3 of Table B store the same kind of data of the

same field

> of table A
> Field1_TableA need to me to mantain a link to data of Table A
>
> So, what do you think is the best and correct solution for that

scenario ?

I am struggling to understand the structure, but if I am correct:

TableA
(
FieldID,
Field1,
Field2,
Field3
)

TableB
(
FieldID,
Field1,
Field3,
FieldID_TableA
)

In this case, I would normalise TableB so it looks as follows.

TableB
(
FieldID,
FieldID_TableA
)

FieldID_TableA should be declared as a foreign key to TableA (FieldID)

If you wanted to, create a view:

CREATE VIEW TEST
(
FieldID,
Field1,
Field3,
FieldID_TableA
)
AS
SELECT B.FIELDID, A.FIELD1, A.FIELD3, B.FIELDID_TABLEA
FROM TABLEA A
JOIN TABLEB B ON (A.FIELDID = B.FIELDID_TABLEA);

Now you can see that

select * from test

returns the data in the format you originally suggest, yet it is not
duplicated in the database itself so you do not need to keep it in
sync.

If TableB contains some other fields, then I can understand the
structure. If however there are no other fields aside from the
FIELDID in tableB, then I don't know what is achieved by creating the
table in the first place.

Normalisation 101: Duplicated data is often a sign that the database
structure needs to be normalised.

Adam