Complex database - help!

[thommo41]

Hi,
Sorry to ask such a full on question with my 1st post, but our sales department want a database to maintain contacts in it. Simple I thought, easy enough. But now, they want it to have a "hierarchical" layout to it, so it can depict a companies structure. Basically, what was a simple company name / contact name database with 2 tables, became something I can't figure out.

They want...

HEAD OFFICE COMPANY
H/O ADDRESS (etc)

H/O CONTACTS

SISTER COMPANY
SISTER ADDRESS (etc)

SISTER CONTACTS

DAUGHTER COMPANY
DAUGHTER ADDRESS (etc)

DAUGHTER CONTACTS

So the company name contains the details of the company office (address, website, main phone etc), but under that, any contacts under that office/company level have records for their contact details (phone, job title, email etc)
But they want 3 levels to it, so they can define the structure of a company, with contacts under that office, tied back to head office, then one level further for any daughter offices, again, contacts under that office.
All of it needs to be tied together so you can see all contacts under the top level, or break it down to see contacts under the level(s) below.

I started trying to normalise this, and failed. My head hurts! I'm not a DB programmer at all, I simply run the IT department and this type of thing ends up on my desk.

Any help much appreciated, even if it's simply "you can't do this with access"

TIA, Alan

 

[Galaxiom]

All companies are stored in the same table. A junction table stores the relationships between them.

The junction table will contain fields:
CompanyID1, CompanyID2, RelationshipID

Contacts are another table. Usually it would have a field for CompanyID but you could use a junction table for that too if a contact could be located in more than one company.

 

[thommo41]

Thanks Glaxiom, but you lost me
A junction table? Is this a term or is it a different type of table? I really am lost...
The contacts could live under any 1 company, but should all be displayed if listing contacts at the top level. Thanks for the starting point, but I think I'm too new to this

 

[Galaxiom]

Junction tables are used to define many-to-many relationships.

Still just an ordinary table but it stores the keys to two or more records in other tables. The records can be in the same table as each other or in different tables.

A company could have multiple sisters and multiple daughters. Lets take the example of multiple sisters. Without the junction table you would require multiple fields, Sister1, Sister2 etc in the company table and it would quickly get out of hand.

The junction table stores each relationship as a separate record so you can have as many relationships as you want without ever changing the table structures.

The RelationshipID in the junction table records whether it is a Sister or Daughter relationship.

 

[thommo41]

Thanks Glaxiom, I think I get the concept, any chance you can outline what tables are required, and the links? Sorry, I know that's essentially asking you to do the job for me

 

[jdraw]

I recommend you read and work through this tutorial. Part of your issue seems to be "listening to the clients requirements". I mean that in a good way --- clients , whether bosses or true clients, often feel they have a "wide-open" set of parameters. The underlying condition is often called "scope creep" - that's where the initial issue , with somewhat defined boundaries, has now been moved to include something new and undefined. Usually, when it's the boss, it's more difficult to say "No" (or let's focus on the primary issue).
The answer may be to start with a list of business facts surrounding the proposed database. You can always design a bigger picture, and only develop certain pieces. At least you have a design picture of where all the pieces fit. The availability of money and people help identify priorities of what can be built when.

There are many existing data models at
http://www.databaseanswers.org/data_models/index_all_models.htm


Here's an example of what is meant by Business facts...
http://www.databaseanswers.org/data_models/bookstore/facts.htm

The facts, as the tutorial shows, is the way to set up tables and relationships.

Good luck.

 

[Galaxiom]

The underlying condition is often called "scope creep" - that's where the initial issue , with somewhat defined boundaries, has now been moved to include something new and undefined.

Oh "feature creep" / "scope creep" is a massive issue for developers.

I don't even bother asking about whether the users will need to be able change the operating parameters. I put everything in tables so it can be easily adjusted and structure the design so that it will ultimately facilitate doing more than they ask now because I just know they will want it later.

Otherwise the redesign is so painful.

I have literally had cases where they wanted it done one way. I asked them would another way be a possibility. Oh no. Just the simple calculation. Then they questioned the results they got while using it revealing that they did actually want the alternative I suggested.

So I changed it only to be told that they really did want it the first way after all because they looked up the requirements again.

Then they look at me like I misunderstood.

My rule now is: If you think of the alternative then include it and allow the choice to be selected in the setup form. I am lucky though because I work for a salary.

 

[jdraw]

Absolutely. You can include such things in a conceptual/logical design, and never actually develop them.... but at least you've considered it "generally".

 

[Galaxiom]

Thanks Glaxiom, I think I get the concept, any chance you can outline what tables are required, and the links? Sorry, I know that's essentially asking you to do the job for me

Particularly horrible but basically functional example attached.

 

[Pat Hartman]

The relationship between companies and their subsidiaries is hierarchical and that makes it 1-many rather than many-many so it doesn't need a junction table.

Each company has one and only one parent company so you would add a ParentCompanyID column to the company table and populate it with the ID of another company if the company is a "child" of another company. If the company is the top of the tree, the ParentCompanyID will be null.

This schema is often called self-referencing since the table has a relationship with itself. This is the same type of relationship you see in an Employee table where you include the ManagerID for each employee. The same relationship type is also used to represent pedigrees except that each animal has two parents. A Sire and a Dam. We don't use this for human genealogy because we also want to be able to represent subsequent marriages and adoptions and that complicates the schema to the point where you need a many-many relationship so that children can actually have multiple mothers and fathers.

The best way to represent this type of data is with the TreeView control. Queries are a also a little different since you have to decide ahead of time how deep you want to go. When you get to that point we can help with the queries or you can look for samples of BOM (bill of material) or self-referencing queries.

 

[thommo41]

Pat, thanks but you lost me there

 

[Pat Hartman]

The schema is simple enough. It only requires two tables. One for companies and one for contacts. I know your request is for specifically THREE levels of companies but that is not how the world works. If you use the correct structure there is no reason to be arbitrary about this.
tblCompany:
CompanyID (autonumber primary key)
CompanyName
CompanyAddress
...
ParentCompanyID (long integer, foreign key to tblCompany.CompanyID)

tblContacts:
ContactID (autonumber, primary key)
CompanyID (long integer, foreign key to tblCompany.CompanyID)
FirstName
LastName
Phone
eMail
...

To create the ParentCompanyID to CompanyID relationship, you will need to add tblCompany to the relationship window twice. Access will suffix the second instance with "_1". The table isn't duplicated. This is just a way to represent the relationship in the window. Then draw the join line from ParentCompanyID in tblCompany_1 to CompanyID in tblCompany. Check enforce RI but do not check Cascade Delete or Update.

Get the tables defined and post the database. Then we'll help you with the queries.

 

[thommo41]

Thanks for that, I've created the DB with the replationship (I think!) though without data in it, or a form/query, I can't get my head around how it all works, in terms of it doing what it needs to do?
Thanks, Alan

 

[Pat Hartman]

I added a query that shows four levels.

 

[thommo41]

Thanks Pat, I think
The purpose of the database was to maintain contacts, whilst also allowing it to show the persons place within a companies (clients) structure.
How do I get that information out, how do I can a form to show contacts (names) within the companies? The more I think about it, the more I think perhaps it's not access...

 

[Pat Hartman]

You need to decide how the interface should work before you can start creating queries. I suggest that you find some examples showing the TreeView control since that is the most visual option you will have. Also examine how the Access Switchboard form works with the Switchboard Items table. I prefer versions created with A2003. The new versions are quite different and are powered by macros. The Switchboard form, replaces its RecordSource query as you move up and down through the menu hierarchy and this method would also work for your purpose. The one problem with the TreeView is that MS has deprecated com objects in A2013 and so A2010 is your limit for this control until/unless some third parth writes a replacement. Send your requests to Luke Chung at FMSINC.com. They create great Access tools.

One thing you will NOT be able to do is create a form that looks like an outline.

If you didn't use Access, what tool would you use? Excel won't be easier if that was what you were thinking and adding/changing companies and contacts will be a nightmare.