Greetings Access Geniuses,

I work for a very large company (100,000+ employees) as a paralegal. I
was hired because of my potent combination of legal and technical
knowledge.


Currently, my team has a serious problem concerning the management of
data relating to a series of products and associated processes. I'll
try to describe the DB I am trying to build that will help alleviate a
significant portion of that problem in time for our huge push during
the period between March and April.


Currently, I am managing 20 different tables.The two main tables are
our major product lines. We'll call them PL1 and PL2. The data
captured on these two tables illustrates each product assicated with
the product lines. We'll call these records PL1.1, PL1.2..., PL2.1,
PL2.2..., and so on.


There are also the following tables that list what we'll call "Simple
Data":


Features ("F"),
ID Number ("ID"),
ID Number 2 ("ID2"),
ID Number 3 ("ID3"),
ID Number 4 ("ID4"),
ID Number 5 ("ID5"),
Fiscal Year End ("FYE"),
Annual Update Date ("AUD"),
Surrender Period ("SP"),
Launch Date ("LD"), and
Status ("S")
G/Non-G ("GNG")


I should note that all the above tables only have one column. Also,
the tables called "ID..." are not in reference to primary keys, but
show product ID's corresponding to different sources. Just trust me
when I say they need five different ones, even though it looks silly
on paper.


Lastly, there are the following tables that list what we will call
"Complex Data":


-Portfolio/Sub ("PS") - This table has two columns, one for Portfolio
and one for Sub.
-TP Version ("TPV") - Simple table with a list of version numbers.
-P Trust ("PT") - A list of the P Trusts
-T Booklet Version ("TBV") - Two columns, one listing the version
names, another with the contents of the
corresponding version.
-Non-P Trust ("NPT") - A list of the Non-P Trusts
-Share Class ("SC") - A list of the Share Classes.


With these tables, the problem I am trying to solve is this. For each
product in a particular PL, we need to see a lot of information (hence
all the tables). If I were to generate a form, one would be able to
select a product, say PL1.1, and instantly see the following
specifically tailored information:


F
ID
ID2
ID3
ID4
ID5
FYE
AUD
SP
LD
S
GNG
PS
TPV
PT
TBV
NPT
SC


With respect to the Simple Data, the relationships are easy. Each of
F, ID, ID2, ID3, ID4, ID5, FYE, AUD, SP, LD, S, and GNG are related to
their corresponding PLs. What's important is that only specific
records from each of these show, however. For example, PL1.1 might
only get F1, F3, F4 and F5, even though the list of F's goes from
1-20. Additionally, it would only get one ID from each of the ID
tables, even though there are 50-60 ID numbers per table. I think you
get the point.


With respect to the Complex Data, the relationships are not so clear
cut. Here is how it breaks down. (We'll only use one PL because the
exact same applies to both PL1 and PL2.)


PL1 has a certain series of PS's associated with it (PS1, PS3, PS4...)
out of a list of about 60 PS's. It also has a series of related PT's
and NPT's. Within the NPT's there are TBV's. Within the TBV's there
are TPV's. Finally, each PS is associated with an SC.


So, PL > PS > PT/NPT > TBV > TPV, and PS > SC.


Where it gets even trickier is the each PS record is associated with a
corresponding SC. Based on the above flow, if you choose a PL record,
you should see all the PS's, PT's, NPT's, and so on associated with it
(in addition to the Simple Data above). However, the problem is that
an SC might be the same (i.e. SC1), but might exist within more than
one TPV associated within different PS's.


I'm 100% certain I did a poor job of explaining that. Sorry. I tried
my best.


Now that you have an overview, I'd like to know the following:


How should I set up the actual relationships? How should I assign
primary keys? Is a form the best way to achieve my purpose? Should I
use this many tables, or combine the data into one table and just
point the relationships to the correct field names? Is there anything
else I should know?


Thanks so, so much in advance for any assistance.


Best,


G


PS - Using Access 2003

Hi and Welcome to UA

The following links will provide most of the information you require.

Suggest you have a good read then redesign your tables based upon the information you will gain by these articles.

Should you run into problems then please post a copy of your Database with your question. Would recommend one question at a time.

Hope this helps you with your endeavours.


[*]Database planning tutorial Part I
[*]Database planning tutorial Part II
[*]argeedblu's FAQ on Design Strategies
[*]Noah's FAQ on Normalization
[*]Jerry's FAQ on Normalization
[*]GroverParkGeorge's PDF on Normalization
[*]Jerry’s FAQ on Fully Qualified Naming
[*]Jerry's FAQ on Glossary
[*]Danny's FAQ on Reserved Words
[*]Autonumbers - What They Are / Are Not
[*]Primary Keys - Natural vs Surrogate
[*]Naming Conventions - Hungarian Notation
[*]Crystal's ACCESS Basics for Programming
[*]Database Answers (free models)
[*]MSKB 283878: Description of the database normalization basics
[*] The Evils of Lookup Fields in Tables

Welcome to Utter Access!

I suspect you have some normalization issues based on what you have described.

It is hard to help you much more without also seeing your actual table structures.

Please post the structures. It may be easier to just post a sample database with sample data. Please replace any confidential data with sample/test data.

I will definitely post a sample as soon as I read through all of the great information givne to me above and make any necessary changes.

So far, I have not assigned any keys. I have built some relationships based on how I think things will work, but they are definitely subject to change.

I'll get to this shortly. Stay tuned!

Gilbert

Just to add to what Boyd and Des have said, giving the real scenario instead of a hypothetical setting is only going to help you. This medium is tough enough to try to get complex thoughts across without having to battle 'scenarios' that are not 'real world'.

When I do include a sample it will be real for certain. No sense in making stuff up at this point. Thanks!

Okay, here is a the DB as it stands now. I have removed all relationships and "normalized" the tables as best as I could based on the information given. Any suggestions as to how to proceed?

I would like to know how to set up the relationships and forgein keys so they work. See my original post above.

Best,

Gilbert

Note: By downloading and/or opening the database, you hereby agree to adhere to the strictest level of confidentiality with regard to its contents, structure, purpose, etc. Do not distribute or share.

Hi Gilbert

Difficult to know where to start relating things really due to the (apparent) complexity of your business model, but I can definitely tell you that tblTrustBookletVersion has repeating groups that need to be in their own table.

Maybe if you could explain what it is you want to model (the abridged version (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif) ) it would make it easier for someone to suggest a workable solution for you

Also, your

<<Note: By downloading and/or opening the database, you hereby agree to adhere to the strictest level of confidentiality with regard to its contents, structure, purpose, etc. Do not distribute or share>>

isn't really valid on a public forum, and it's up to you to only put in non-sensitive test data that others can look at to try and understand/help you along the way (not that I'm suggesting any UA members would do anything untoward!!).

If you feel there's any confidential/sensitive data within the db you posted, please contact an admin ASAP to have them remove the attachment for you

I think you have a good start here, but there is a lot of work to be done. When Allan said that tblTrustBookletVersion was incorrect, he was correct.

The use of repeating Names mean Nothing. What is the difference between Contents1 and Contents2. Bear in mind you should be able to come back to this DB in two years time and understand what is what. Also Allan and I should be able to understand.

So all these Content things need descriptive names.

How do you envisage table say, tblSeasonsProducts fits it to the whole scheme. Which other table/tables is it related to and how.

tblFiscalYearEnd. What is the purpose of this table. In the land of Aus this is always 30 June. I think in the USA it is always 31 February. So this table may not be needed.

Re you confidentially clause I promise to only share this information with the few million people that vist this site.

Des,

When you say the fiscal year end is June 30 in Australia, you forgot to mention that you only need to fill out a tax return every leap year (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)


(*Note - any ATO officers reading this, my tax file number is 555 12345)

Fiscal Year End can vary from company to company in the US. No set dates that required to be adhered to. That's why I made the code archive submission for calculating 'fiscal year dates'.

Unfortunately, this is not a good start on your project.

Apart from the already mentioned repeating groups in one table, you appear to be repeating attributes and/or entities across several tables. Without knowing your business model, it is very difficult to give specific advice on your structure.

Hi Jerry,

I'm not too sure I understand what you mean by repeating attributes. May I ask for a little bit of clarification?

As far as the repeating groups on one table, I'll separate them out into new tables and repost.

Thanks for the reply.

Gilbert

Hi Alan,

As soon as I fix the repeating groups problem by extracting items and adding them to their own tables, I will post a new workup with an attempt at describing the relationships between the tables.

There is definitely a lot going on and I more than admit I'm out of my league. Thank goodness there are resources like this and people like those assisting out there to help make things slightly smoother.

With regard to my "disclaimer," there is at least some validity there. It cannot hurt to express some vigilance in terms of how I wish for my database to be handled by the end user. At the very least, I'm asking, know what I mean?

I completely understand, however, that this is indeed a public forum. Plus, it's very obvious that people here are serious about their and professional in their operations. I'm not worried.

But again, thank you very much.

Gilbert

You have two tables for ActNumbers
Two tables for some type of ID number
A fiscal year end table which can't possibly serve any purpose
A table for some type of Form?
Two tables for status
A launch date table which also can't possibly serve any purpose
Two different products tables
And additional tables that are probably not 1-m attributes to some other entity.

Since I don't know your business model, I am only basing my assumptions on the table and field names you have in the DB. If you would post a better description of your business model it would be easier to assist with the structure.

Hi Jerry,

Okay, we're on the same page in terms of your question. I was hoping you weren't asking something more technical ("attributes") that I simply wasn't getting, and since you're clearly one of the serious experts around these parts, I admit I was a little worried.

I am currently working on a response which will include at least three items, (1) the updated DB, based on comments from the discussion; (2) a sample of the output we are looking for; and (3) a better description of what we're trying to accomplish here, along with some preliminary relationships (on the DB) as I see them so far. Of course, these are not set in stone and I expect they will change significantly as things progress.

What I can tell you for certain is that the tables each list necessary data. Whether I can combine that data into different tables is subject to input received here.

Again, thank you and everyone else here for the continued assistance.

Best,

Gilbert

A better place to start understanding your business model is for you to list on paper all of the attributes the DB must track. Once you have all of these "fields" you can then start the process of organizing them into a coherent normalized structure. I have illustrated a fairly effective method for taking your attributes(fields) and letting them organize themselves into the proper entity/attribute/relationship structure. This method is called Fully Qualified Naming. Using this method you fully qualify the names of each attribute. Once this is done you will see a pattern emerge that starts grouping the attributes into their related entities. You can find my post on this method in the Access FAQA forum.

Hi Jerry,

To be certain, I read a lot of your material, including the suggestions above, as well as your piece on Fully Qualified Naming.

Also, I have been doing this on "paper" while further solidifying my understanding of the model via continued communications with my team.

One positive result of the above, which you will see shortly in a subsequent post, is that I was able to place the Trust Booklet Names data into the Trust Prospectus Versions Table, where it actually belongs, as opposed to breaking it out into separate tables. More on this soon.

Best,

Gilbert

Hi Everyone,

Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.

The following changes have been made to the DB:

-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model.
-Added descriptions to all non-PK fields.
-Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.

Our Business:

I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.

Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.

In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.

Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.

Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.

Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).

Since the features can be so easily broken down, should I add them to their own tables?

Please consider this as you give your advice on how to acheive my desired goal.

I hope this makes sense.

And again, thank you all so very much for your help thus far.

Best,

Gilbert

Hi Everyone,

I guess I scared you all away! Hope to hear from someone soon.

Thanks again,

Gilbert