db design related questions

  • Thread starter Thread starter Francois Malgreve
  • Start date Start date
F

Francois Malgreve

hi all,

I have 2 kinds of finanacial objects in my design, nevertheless they both
should "share" the same logical ID.
Let say i have 2 types defined as being OBJECT1 and OBJECT2 (2 being
different kind offinancial transaction)
They are totally different and do not share any field exceot there ID. Which
makes that any kind of finanacial transaction will have a different ID

as for exemple, let say i have
transaction 1: OBJECT1, ID:00001
transaction 1: OBJECT1, ID:00001
transaction 1: OBJECT1, ID:00001
transaction 1: OBJECT1, ID:00001
 
please disregarde the incomplete original message,

the full message is :

hi all,

I have 2 kinds of finanacial objects in my design, nevertheless they both
should "share" the same logical ID.
Let say i have 2 types defined as being OBJECT1 and OBJECT2 (2 being
different kind offinancial transaction)
They are totally different and do not share any field exceot there ID. Which
makes that any kind of finanacial transaction will have a different ID

as for exemple, let say i have
transaction 1: type of transaction OBJECT1, ID:00001
transaction 2: type of transaction OBJECT1, ID:00002
transaction 3: type of transaction OBJECT1, ID:00003
transaction 4: type of transaction OBJECT2, ID:00004
transaction 5: type of transaction OBJECT2, ID:00005
transaction 6: type of transaction OBJECT1, ID:00006

I assume you got the idea.
I can easily see how i can achieve this in OO design. I would have a mother
transaction object, probably abstract (which would define my ID) and 2 sub
classes which are the 2 different type of transactions.

Now, in a Relational DB, how can I achieve to have the same ID for 2
different tables. These ID would ideal be autogenerated by the DB (like
autonumber in ACCESS, Identity in SQL Server).
If it is not possible to use those autogenerated Id for my case then I would
welcome any proposal. Anyway my main issue is being able to achieve this
common ID for both objects which would also achieve some kind of inheritance
mechanism in the RDB.

Any help would be greatly appraciated as I am kinda stuck on this.

Best regards,

francois
 
You didn't specifically say that your "transactions" are
to be stored in separate database tables, but that seems
to be implied in your question. Assuming that is the
case, you want to avoid using "AutoNumber" or IDENTITY
because they are specific to a single table.

Alternatively, you could always have one table that holds
an ID number (which would be AutoNumber, IDENTITY, etc.)
in one column, a transaction type indicator in a second
column, and then a third column would contain a key to
some other table. That "other" table would vary
according to transaction-type - there would be a separate
table for each transaction-type.

Otherwise, you need a singleton object that issues IDs
sequentially - and doesn't care which type of transaction
the ID number is going to be used for. Your business
logic obtains that sequential ID number and inserts it
into whatever transaction it needs to create at the
moment.

If you happen to be using Oracle, this is easily
implemented with a SEQUENCE that does exaclty this.
Oracle takes care of ensuring uniqueness.

If your using a DB (e.g. SQL Server) that does not
support SEQUENCE, you can emulate the same
functionality. The key is to single-thread it so that it
does not issue the same ID number to multiple processes.

HTH
 
Thank you very much for your information.
Sure the different objects have to be stored in different tables because
their fields are too different and describe quite different businesses.

For the singleton object, I am not sure if I can implement it as I am
working in an ASP.NET application. I need to find out about that.
Also what do you think about a revision of your first proposal where I would
have a table generating the ID but having only that field and no direct link
to any other table. As the relationship between mother and inherited type is
always one to one, I do not need a Foreign Key. When I insert a new
transaction object, I could just do the insert in the Key table (the mother
table), get the value, and use it as the PK of the child table when doing
the insert in the child table. The PK of the childs table would NOT being
generated by themself but only by the mother table which is responsible for
it.
What do you think?
Basically what i am trying to say is why do we need FK in the mother table
as the relationship is one-to-one. also why would we need a column with the
transaction type? I cannot see any use of the 2 extra fields but maybe I
missed something and it would be greatly welcomed if you could clear out
this point.

Best regards,

Francois
 
Back
Top