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.

 

[thommo41]

Pat, thanks but you lost me there

 

[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

 

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