Advice on database design requested

Hi all,

(warning - long post ahead)

I have been tasked with designing a database for my company which will
store trade information (it is a financial firm). It will need to
export this info into an excel file while converting some of the data
into an export format (example - we use B for buy, the firm we export
to uses BY). Eventually, accounting will also need reports from the
data.

I am inclined to use Access for this project since I am more familiar
with it. Would it be better to try and do it with SQL server? If I do
create it in Access, will it be hard to port to SQL server later?

Assuming I do it in Access, here's what I had in mind. The data will
consist of info about the trades which will be entered in by one
trader. This table will contain the following fields:

tblTrades
-AcctNum - the client's account number - should autofill client name
field - this info will be taken from a separate Accounts table, but
used should have option of adding new records.
-Price - price trade is made at
-TradeNum - the trade's "serial #" which is the primary key of this
table
-TradeDate - date trade is made - defaults to current day
-Quantity - number of shares
-SecurityType - user will choose from a list - this list will be
maintained in a separate SecurityType table
-Client name - will be autofilled after choosing the acct number
-CUSIP - a standard code for the trade - 9 char alphanumeric code -
user will fill in info in the next four fields which will be used to
compute the CUSIP. The CUSIP and associated info should be written to
a separate CUSIP table
-(four fields associated with the CUSIP)

Now, the Accounts table will be related to tblTrades by the acctnum
field (which is the pri key for tblAccounts). The SecurityType table
is related to tblTrades through the SecurityType field which is the
pri key for SecType. The same for tblCUSIP, which is linked to
tblTrades with it's pri key, CUSIP. Should I define these
relationships in the Access rel manager? I'm inclined to base my form
on a query which would relate the data and leaving them unrelated
otherwise - is this bad? Does anyone see any fatal design flaws here?
It's not a terribly complex db...

Assuming the basic premise is OK, my next thoughts go to form design,
which I'm not very familiar with. I created the above db and created a
data entry form for the user - tabular format, one line across to
enter data in (so UI is close to excel) and a continuous form since
the user needs to see all the previous trades entered. Would i be
better off using a subform to display the previous data?

I based the form I created off a query that pulls all the fields I
need on the form. In that query I defined the relationships. I used
unbound combo boxes to get the data for acctnum, sectype, and CUSIP.
Choosing the acct number fills in the client name. Everything was
working well until I added the CUSIP table and field - now when I
enter a number in the CUSIP (which should add that number to the CUSIP
table) the db tells me "cannot find a record in tblCUSIP with key
matching field CUSIP" - I got around it with an "after update" code
which adds the record, but is the basic premise here wrong?

I have tons of other questions, but I'll research them myself. If some
gurus out there could just let me know if I'm way off track, I'd
really appreciate it.

Cheers,

Jim

re: Advice on database design requested

On 20 Aug 2004 21:19:30 -0700, armstrongjc@hotmail.com (James
Armstrong) wrote:

Please see comments in-line.
-Tom.

[color=blue]
>Hi all,
>
>(warning - long post ahead)
>
>I have been tasked with designing a database for my company which will
>store trade information (it is a financial firm). It will need to
>export this info into an excel file while converting some of the data
>into an export format (example - we use B for buy, the firm we export
>to uses BY). Eventually, accounting will also need reports from the
>data.
>
>I am inclined to use Access for this project since I am more familiar
>with it. Would it be better to try and do it with SQL server? If I do
>create it in Access, will it be hard to port to SQL server later?[/color]
That's not the best reason to use Access. Frankly if I knew my
bank/broker would have my account info in an Access db, I would
consider moving.
SQL Server is a much more robust storage for your data. It is
exceedingly hard to corrupt. Access is too easy to corrupt. SQL Server
is also easy to use: if you can create tables in Access, you can
create them in SQL Server. Same goes for queries.
Port later can be hard if you use Access-specific features such as
calling a function from a query.
[color=blue]
>
>Assuming I do it in Access, here's what I had in mind. The data will
>consist of info about the trades which will be entered in by one
>trader. This table will contain the following fields:
>
>tblTrades
>-AcctNum - the client's account number - should autofill client name
>field - this info will be taken from a separate Accounts table, but
>used should have option of adding new records.
>-Price - price trade is made at
>-TradeNum - the trade's "serial #" which is the primary key of this
>table
>-TradeDate - date trade is made - defaults to current day
>-Quantity - number of shares
>-SecurityType - user will choose from a list - this list will be
>maintained in a separate SecurityType table
>-Client name - will be autofilled after choosing the acct number
>-CUSIP - a standard code for the trade - 9 char alphanumeric code -
>user will fill in info in the next four fields which will be used to
>compute the CUSIP. The CUSIP and associated info should be written to
>a separate CUSIP table
>-(four fields associated with the CUSIP)[/color]
Not sure about the need to write CUSIP to its own table. I don't think
you made the case here, but you probably have a good reason.
[color=blue]
>
>Now, the Accounts table will be related to tblTrades by the acctnum
>field (which is the pri key for tblAccounts). The SecurityType table
>is related to tblTrades through the SecurityType field which is the
>pri key for SecType. The same for tblCUSIP, which is linked to
>tblTrades with it's pri key, CUSIP. Should I define these
>relationships in the Access rel manager? I'm inclined to base my form
>on a query which would relate the data and leaving them unrelated
>otherwise - is this bad? Does anyone see any fatal design flaws here?
>It's not a terribly complex db...[/color]
You MUST create the relationships in the Relationships Manager.
Enforcing referential integrity (RI) is one of the most important
things an RDBMS does for you. Doing this at the application level is a
recipe for disaster (and future work for me - I see these databases
too often :-)). I have a rule that has never proven false yet: if a
database does not enforce RI and it is a non-trivial db and it has
been used for a non-trivial amount of time, I *will* be able to find
bad data.
Let that not be my trade which is no longer related to my account
record.
[color=blue]
>
>Assuming the basic premise is OK, my next thoughts go to form design,
>which I'm not very familiar with. I created the above db and created a
>data entry form for the user - tabular format, one line across to
>enter data in (so UI is close to excel) and a continuous form since
>the user needs to see all the previous trades entered. Would i be
>better off using a subform to display the previous data?[/color]
No, this is fine. A subform typically is used to show the Many side of
a one-to-many relationship, e.g. the many trades for this account.
[color=blue]
>
>I based the form I created off a query that pulls all the fields I
>need on the form. In that query I defined the relationships. I used
>unbound combo boxes to get the data for acctnum, sectype, and CUSIP.[/color]
Unbound???
Typically if you have a form to add trades, you base it off of a query
on tblTrades alone. Think of comboboxes as a way for humans to enter
ID values. So they have 2 columns: for example hidden AcctNum field
and visible AccountDescription field. This combo is bound to the
tblTrades.AcctNum field on your form.
[color=blue]
>Choosing the acct number fills in the client name. Everything was
>working well until I added the CUSIP table and field - now when I
>enter a number in the CUSIP (which should add that number to the CUSIP
>table) the db tells me "cannot find a record in tblCUSIP with key
>matching field CUSIP" - I got around it with an "after update" code
>which adds the record, but is the basic premise here wrong?[/color]
I'm afraid indeed basic premise may be wrong. See previous comment.
[color=blue]
>
>I have tons of other questions, but I'll research them myself. If some
>gurus out there could just let me know if I'm way off track, I'd
>really appreciate it.
>
>Cheers,
>
>Jim[/color]
Some additional advice (I'm assuming you are an employee at this
firm):
Tell your boss: you want this done right and you know you need to
acquire some new skills. You want to buy and study the Access
Developer Handbook version 2000 or above, the section about Access
Data Project. You want to create an ADP against a SQL Server back-end.
In the next 2 weeks you'll put 15 hrs/week of your own time into
getting up to speed with this new material. You'll also spend some
fraction of your time at work studying the same material. You'll write
a few sample forms that perhaps can be re-used in the main app, but
you'll write them mainly to learn. Then after these 2 weeks, (and
after the functional specification is complete) you'll write this app,
still using your familiar skills of designing Access forms and
reports, writing VBA code, but now in this powerful new environment.
Best of luck.

re: Advice on database design requested

Hi Tom,

Thanks so much for taking the time to reply. Your comments definitely
cleared a few things up and pointed me in the right direction. I will
start creating this DB as a SQL database w/ an ADP to access it, and
think that will work well.

If you have the time, I just have another question about the general
table design. You mentioned some confusion as to why I was putting the
CUSIP in its own table. Let me try and clarify -

The main trade table is just there to hold the info on the trades.
Because I don't want to use industry terms you may or may not be
familiar with, I'll equate this to an orders DB.

So we have our orders table, where a clerk will enter in an order.
Fields will include Ordernumber (pri key since it is unique),
Customer, CustomerAddress, Product, and Shippingtype. (Let's keep it
very simple for discussion's sake)

The clerk does not enter the ordernumber, that is autogenerated. Now,
customer and customeraddress are like my CUSIPs. The clerk wants to be
able to choose a customer from a list of existing customers. Since
there is a separate customer table with their addresses, the field
"customeraddress" should not exist in the orders table, it should
simply pull that info from the customers table, right? So my clerk
selects "Tom" from the list and Tom's address pops up. The only data
stored in the orders table is "Tom" which links it to the customers
table and that is where the address info is found. This is why I put
CUSIPs in a separate table - there is associated "address" info for
them which does not need to be in the trades table. Now here's the
tricky part - the clerk takes an order for Jim, who is not in the
dropdown box. So the clerk should be able to enter the name Jim and
get prompted for the address info, which will then be written to the
customer table so it will be available for selection later on. So my
trader chooses from CUSIPs in the table, but if one is not there, she
enters in the associated info, this gets written to the CUSIP table,
and it is available in the drop-down for the future. All I need in my
trade table is the CUSIP, not the associated data. What is the
cleanest way to accomplish this?

Moving on, the ShippingType would be like my SecurityType field -
there will always be the same 3 or 4 choices (like UPS, FedEx,
Airborne, etc) - I put stuff like this in a separate table so that
when a new type becomes available, I just add it in that table w/o
having to change any of my queries, forms, etc... Is this the proper
way to create a lookup table?

Anyway, the only thing I'm concerned about is the CUSIPs - I just want
to make it easy and simple for the user to choose from existing data
or add new data from the same form. Is this possible?

I will be buying the dev handbook today, but am I correct right now in
assuming that with the ADP I create my tables and queries in SQL and
then just use access DP forms and reports to push and pull the data?
That sounds like exactly what I need.

Thanks so much,

Jim

re: Advice on database design requested

On 23 Aug 2004 06:16:26 -0700, armstrongjc@hotmail.com (James
Armstrong) wrote:

See comments in-line.
-Tom.
[color=blue]
>Hi Tom,
>
>Thanks so much for taking the time to reply. Your comments definitely
>cleared a few things up and pointed me in the right direction. I will
>start creating this DB as a SQL database w/ an ADP to access it, and
>think that will work well.
>
>If you have the time, I just have another question about the general
>table design. You mentioned some confusion as to why I was putting the
>CUSIP in its own table. Let me try and clarify -
>
>The main trade table is just there to hold the info on the trades.
>Because I don't want to use industry terms you may or may not be
>familiar with, I'll equate this to an orders DB.
>
>So we have our orders table, where a clerk will enter in an order.
>Fields will include Ordernumber (pri key since it is unique),
>Customer, CustomerAddress, Product, and Shippingtype. (Let's keep it
>very simple for discussion's sake)[/color]
Typically CustomerAddress would not be in the Orders table, but in the
Customers table. Only CustomerID would be in the Orders table, and
armed with that, you can lookup all customer info in the Customers
table.

[color=blue]
>
>The clerk does not enter the ordernumber, that is autogenerated. Now,
>customer and customeraddress are like my CUSIPs. The clerk wants to be
>able to choose a customer from a list of existing customers. Since
>there is a separate customer table with their addresses, the field
>"customeraddress" should not exist in the orders table, it should
>simply pull that info from the customers table, right?[/color]
Right.
Perhaps have a combobox "select customerID, customerName from
Customers", that is bound to the Orders.CustomerID field. Column
widths are "0;1".


So my clerk[color=blue]
>selects "Tom" from the list and Tom's address pops up.[/color]
That dropdown I just described can have a couple more (perhaps hidden)
columns, and in the cboCustomers.AfterUpdate event you can write:
lblCustomerAddress.Caption = cboCustomers.Column(2)
lblCustomerZip.Caption = cboCustomers.Column(3)

The only data[color=blue]
>stored in the orders table is "Tom" which links it to the customers
>table and that is where the address info is found.[/color]
I would think the Orders table stores 123, which is Tom's CustomerID
(autonumber).

[color=blue]
>This is why I put
>CUSIPs in a separate table - there is associated "address" info for
>them which does not need to be in the trades table. Now here's the
>tricky part - the clerk takes an order for Jim, who is not in the
>dropdown box. So the clerk should be able to enter the name Jim and
>get prompted for the address info, which will then be written to the
>customer table so it will be available for selection later on. So my
>trader chooses from CUSIPs in the table, but if one is not there, she
>enters in the associated info, this gets written to the CUSIP table,
>and it is available in the drop-down for the future. All I need in my
>trade table is the CUSIP, not the associated data. What is the
>cleanest way to accomplish this?[/color]
As I've argued above the address info can be in the Customer table.
The cboCustomers is limit-to-list, and has a NotInList event. If user
types a non-existing name, you pop up the Customers form MODALLY and
have user create a new record. Then set return value of NotInList to
requery the dropdown.

[color=blue]
>
>Moving on, the ShippingType would be like my SecurityType field -
>there will always be the same 3 or 4 choices (like UPS, FedEx,
>Airborne, etc) - I put stuff like this in a separate table so that
>when a new type becomes available, I just add it in that table w/o
>having to change any of my queries, forms, etc... Is this the proper
>way to create a lookup table?[/color]
Perfect.
[color=blue]
>
>Anyway, the only thing I'm concerned about is the CUSIPs - I just want
>to make it easy and simple for the user to choose from existing data
>or add new data from the same form. Is this possible?
>
>I will be buying the dev handbook today, but am I correct right now in
>assuming that with the ADP I create my tables and queries in SQL and
>then just use access DP forms and reports to push and pull the data?
>That sounds like exactly what I need.
>
>Thanks so much,
>
>Jim[/color]