Modeling orgs, suborgs, their employees, and addresses for all

[Raincheck]

I have encountered this multiple times and am never sure how to best model. I have organizations, each organization can have multiple locations. Each location will have multiple employees and I need to store addresses, not just for the business locations, but also the home address for each employee. I am never sure how to best model. It seems addresses should all be together but it also seems wrong to have some address records map to the Locations tal and some to the Employees. Do I put Locations and employees all in the same table (Parties)?

And this time, for each address in the database, there will be tracked their 3 political districts. Is there a standard or best practice or general advice? Thanks.

[CJ_London]

Is there a standard or best practice or general advice?

only using normalisation rules sensibly. What works for you, works for you.

You might have a table for each

organisation
...organisation address
......employees
...........employee address

all joined on PK/FK.

There may be differences between the two address tables but either way easier to manage

or you could have a recursive table which relates to itself to describe the structure

tblEntitites
EntityPK autonumber
entitytype - number or text (e.g. organisation/employee/address)
ParentFK...long - links back to PK of this table

(The 'top parent' can be identified since its ParentFK field will be blank)

tblAddresses
AddressPK autonumber
EntityFK - long
Addr1...text
Addr2...text

PK....Type.....Parent
1......Org.......null
2......Addr.....1
3......Addr.....1
4......Emp......2
5......Emp......2
6......Emp......3
7......Emp......4

so Org#1 has 2 addresses, address#2 has 2 employees and emp#4 has a subordinate (#7)

So your address table could be linked to any of these records.

This structure requires more management but is more flexible. For example you would not link the address table to the Org entity (because you would be linking it to the address entity instead). This means you will probably need to use left joins to display all records.

Common fields across all, or most entities, can be included in the entities table, but otherwise you will need additional tables for say a list of products the organisation makes (might be tied to the org, entity or the address entity) or employee qualifications. But you often simplify that with a table of single field values e.g.

tblElements
ElementPK autonumber
EntityFK long
ElementType number or text - for addresses it might be addr1, addr2, for orgs it might be products, and emps qualifications
ElementValue text (numeric values would be stored as text)
ElementOrder number (optional to sort elements so the can be displayed in the right order

PK...EntityFK..Type...Value...........Order
1.....2..............Prod....bottles............nu ll
2.....3..............Prod....bottle tops......null
3.....3..............Addr1..Harvey Street...1
3.....3..............Addr1..Grantchester.....2
4.....7..............Qual....First Aider.......null


Also, when mapping out your data, you need to know the number of levels (or at least the maximum number) for a single query. Otherwise you need to have a recursive vba function to scan up and down the entities table)

Because this is more complex, It is very flexible but queries can take longer to run so more suited to small datasets - so don't include entities such as invoice headers and lines into the entities table

[orange]

As Ajax has said, what works for you, works for you. It is not a trivial subject.

There are several articles concerning hierarchies and modeling them.
Here are a few that may help with understanding and concepts re implementation.
They aren't necessarily straight forward, but do represent the topic generally.

https://stackoverflow.com/questions/...ional-database

https://martinfowler.com/apsupp/accountability.pdf

http://tdan.com/a-universal-person-a...ata-model/5014

Good luck -- probably more info than you want/need.