The database

The database contains a lot of tables, many are configuration table and many contain actual data. Configuration data is classified into two categories, Definition and Configuration where definition is a simple lookup value, used only by the users of the system and mostly ignored by the system itself. Configuration on the other hand contains values that is used to control the logic of the program.

The central data table is cust_entity where customers are stored. Both companies and persons are stored in the same table. The table contains only names and codes that belong to the entity, with some default links for connected information. Everything else is stored in records connected to the cust_entity. The reason not to have two tables, cust_company and cust_person is to allow linking extra information directly and through a single link to the entity and be able to have the same functions regardless if it's a company, person or something else. Companies and persons are linked together in a separate table called cust_role, since the entity table has both companies and persons, the link table allows linking of persons - person, person - company, company - company and also company - person. Cust_entity has a field that contains either C for company or P for person, it can of course also be expanded to allow for other kinds of entities should the need for that be discovered.

The behaviour of the cust_role table is decided in the roletype table, here you define the roles that should be used by entering the type of entity the up link should go to, the type of link the down link should go to and also enter descriptive text for both links. For example, linking a C entity to a P entity the descriptions might be Company and Employee, or Employed at - Employee if that's preferred. Having a separate table for the roles also means that you can have several roles to a company. If owner and member of board is interesting to know, you can have one person having both those roles to a company for example.

The combination of cust_enitity and cust_role gives a very flexible system, it also gives some extra work in implementing how it should be handled, but so far the benefit of flexibility has been greater than the extra work required to implement it.

The database structure itself is fairly standard, surrogate keys are used almost everywhere mostly because of speed issues. Addresses are stored in one table linked directly to cust_entity, email, fax and phone numbers are stored in the same table as a companion to the address table and is called eaddress. This is also a shortcut that works well for the user in an interface but sometime gives problems when you want to select an email. Usually the problem is that a customer can have more than one email, more than the fact that it's mixed up with phone number. The type of electronic address are defined in a separate table where you also tell the system what names are phone and email. This allows for future expansions in adding VOIP access numbers and other future ways of communication that can be described in a single line of text.

The rest of the tables connected to cust_entity follow the same model, usually there's a type associated, if the data is processed automatically at some point, there's usually one or several type tables that are linked to get the description and if there are more tables in turn connected the graph will usually show a star shape with cust_entity in the middle. There's also a fair amount of inter linking between table when that's needed.