Database Design question
srdaniel
firebird database as we slowly migrate from paradox to firebird.
One of the paradox tables we have is basically the yellow post-it notes,
that can be attached to customers and/or orders.
However a new request has come down which says also be able to attach
the post-it notes to other areas, say statements, invoices, etc.
Now most of these tables are all designed with one primary key value
that is auto generated and the user never sees. A couple of the tables
have dual primary keys.
Customers->customer_ref
Orders->customer_ref,order_ref
Statements->statement_ref
Invoices->invoice_ref
...
The current post-it note table can only be attached to customers or
orders and has the following fields:
post-it_Ref (unique integer)
customer_ref integer
order_ref integer
however with this design the post-it's are limited to only customers or
orders. In order to be able to attach multiple post-it's to other
areas(tables), should I simply add a (foreign) key field for each area
that I want to be able to add post-it's to? So if I wanted to add
statements and invoices I would simply add two more fields?
post-it_Ref (unique integer)
customer_ref integer
order_ref integer
statement_ref integer
invoice_ref integer
...
or should I come up with something more like this?
post-it_ref (unique integer)
attached_to_source (?)
attached_to_fields (varchar)
attached_to_values (varchar)
The post-it table would then be filled out something like the following
sample post-it records.
Customer
------
post-it_ref=1
attached_to_source=Customer
attached_to_fields=customer_ref
attached_to_values=123
Order
-----
post-it_ref=2
attached_to_source=Order
attached_to_fields=customer_ref;order_ref
attached_to_values=123;6
Invoice
-------
post-it_ref=3
attached_to_source=Invoice
attached_to_fields=Invoice_ref
attached_to_values=888
The selectivity of the attached_to_values would be pretty high I think.
There is usually one one post-it note per customer/order, however they
can have more than one. I don't foresee more than an average of one for
any other area (statements, invoices, etc) either.
I thought about viewing this from the other direction and making the
various areas aware of the post-it's instead of the post-it's keeping
track of what they are attached to. However, the scope of the work order
is not to rip apart the existing post-it system just extend it. So I
want to create a firebird version of the current paradox post-it table
but make it expandable for when the paradox version does get ripped out
and replaced with the firebird table version later. So any new areas
that need post-it capability will use the new firebird version.
Brian Bushay TeamB
I don't understand the Attach_to_values examples you show.
All of the fields you want to attach to are integer.
A source field and an attach_key value field should be all you need.
You can have the attach_to_field in your query or code where you fetch the
related postits
--
Brian Bushay (TeamB)
Bbu...@NMPLS.com
Brad Prendergast
<44c6...@newsgroups.borland.com>:
>The current post-it note table can only be attached to customers or
>orders and has the following fields:
>
> post-it_Ref (unique integer)
> customer_ref integer
> order_ref integer
>
>however with this design the post-it's are limited to only customers
>or orders. In order to be able to attach multiple post-it's to other
>areas(tables), should I simply add a (foreign) key field for each
>area that I want to be able to add post-it's to? So if I wanted to
>add statements and invoices I would simply add two more fields?
YOu might gain a bit more flexibility by referencing the post-it_Ref
from the other tables. This eliminates the need to add fields to the
post-it table in the event of expansion. You can either add a FK to the
post-it_ref from the tables that use the post-it_ref. Or if you want
to avoid table changes come up with a post-if cross reference table.
Depends on what you're specs deem you need.
--
Brad Prendergast
"The only difference between me and a madman is that I'm not mad." --
Salvador Dali (1904 - 1989)
Rick Carter
As a TeamB member, are you not aware of the policy against overquoting?
http://info.borland.com/newsgroups/netiquette.html
Please refer to point 1 on that page.
Rick Carter
cart...@despammed.com
Chair, Delphi/Paradox SIG, Cincinnati PC Users Group
--- posted by geoForum on http://delphi.newswhat.com