473,887 Members | 2,275 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 13 '05 #1
3 1675
On 20 Aug 2004 21:19:30 -0700, ar*********@hot mail.com (James
Armstrong) wrote:

Please see comments in-line.
-Tom.

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? 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.

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) 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.

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
relationship s 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... 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.

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? 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.

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. 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 AccountDescript ion field. This combo is bound to the
tblTrades.AcctN um field on your form.
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'm afraid indeed basic premise may be wrong. See previous comment.

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

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.
Nov 13 '05 #2
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
"customeraddres s" 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
Nov 13 '05 #3
On 23 Aug 2004 06:16:26 -0700, ar*********@hot mail.com (James
Armstrong) wrote:

See comments in-line.
-Tom.
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) 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.


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
"customeraddre ss" should not exist in the orders table, it should
simply pull that info from the customers table, right? Right.
Perhaps have a combobox "select customerID, customerName from
Customers", that is bound to the Orders.Customer ID field. Column
widths are "0;1".
So my clerkselects "Tom" from the list and Tom's address pops up. That dropdown I just described can have a couple more (perhaps hidden)
columns, and in the cboCustomers.Af terUpdate event you can write:
lblCustomerAddr ess.Caption = cboCustomers.Co lumn(2)
lblCustomerZip. Caption = cboCustomers.Co lumn(3)

The only datastored in the orders table is "Tom" which links it to the customers
table and that is where the address info is found. I would think the Orders table stores 123, which is Tom's CustomerID
(autonumber).

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? 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.


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? Perfect.

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


Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
1442
by: obhayes | last post by:
Hi, I have two tables Table A and B, below with some dummy data... Table A (contains specific unique settings that can be requested) Id, SettingName 1, weight 2, length Table B (contains the setting values, here 3 values relate to weight
18
2910
by: Mickey Segal | last post by:
On comp.lang.java.programmer we are discussing problems created for Java programs by pop-up blockers (in the thread "showDocument blocked by new microsoft pop-up blocker"). Our problem is that Java's showDocument method, which opens new browser windows, is blocked by some pop-up blockers. The showDocument method is blocked even if the user clicked a button in a Java program to call showDocument. As a result, a type of user-initiated...
4
2078
by: Smriti Dev | last post by:
Hi There, I'm creating a MS ACCESS database using Ms Access 2000 to store Interpretation requests by different departments in a hospital and Interpreter availability. All internal departments will be calling in or faxing their Interpretation requests to Intake staff at the Interpretation department and the intake staff will feed the request into the database. However, their is one department, Rehab, that is going to be going to be able...
9
2673
by: Laban | last post by:
Hi, I find myself using static methods more than I probably should, so I am looking for some advice on a better approach. For example, I am writing an app that involves quite a bit of database operations on purchase orders and inventory. I have created a PurchaseOrder class and Inventory class to encapsulate operations like creating POs, finding items, etc. These two classes are used extensively from different parts of the app.
2
3194
by: Brian | last post by:
NOTE ALSO POSTED IN microsoft.public.dotnet.framework.aspnet.buildingcontrols I have solved most of my Server Control Collection property issues. I wrote an HTML page that describes all of the problems that I have encountered to date and the solutions (if any) that I found. http://users.adelphia.net/~brianpclab/ServerControlCollectionIssues.htm This page also has all of the source code in a compressed file that you are free to download...
1
9675
by: David Van D | last post by:
Hi there, A few weeks until I begin my journey towards a degree in Computer Science at Canterbury University in New Zealand, Anyway the course tutors are going to be teaching us JAVA wth bluej and I was wondering if anyone here would be able to give me some tips for young players such as myself, for learning the language. Is this the best Newsgroup for support with JAVA?
4
1044
by: Jerry | last post by:
Hello! I'm creating an application where users can enter their hours worked on certain projects and have them saved to a database. Right now there are about 60 different projects and that number can increase or decrease at any time. I'm looking for some advice on how to best design the windows form for users to enter hours for the projects they worked on that week. I was thinking about using a comboBox to list each project with a text...
53
4774
by: Hexman | last post by:
Hello All, I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm using some VB6 code, .Net2003 code, and .Net2005 code. I'm developing in vb.net 2005. This test sub just reads an input text file, writing out records to another text file, eliminating records that have a '99' in them (it is similar to a CSV file). Some of my concerns are:
23
2452
by: JohnH | last post by:
I'm just recently come to work for an auto brokerage firm. My position involves performing mysterious rites, rituals and magick in order to get information out of their access database. This is due to the fact that the "designer" they hired had an unfortunate ability to tink around with a program far beyond her reach. She had no concept of any of the principles of database design, so what i'm left with is a junkpile. The only reason I...
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11173
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7141
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6011
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.