Sharing ideas, A worked example

Sharing your ideas – An example:

How to share multiple files with other people 

When you need to share information with others over a period of time, maybe as part of a project, and where you need to reference it and update it, you will need a way of storing it and a way of tracking it so that it does not get confusing for the people who have to use it.

This page looks at a real example of a ‘shared files’ database design that I once had to do. This was required to keep track of files that are shared with others and where the files can be updated by different people and in different ways. I will also use a quick parallel example of a ‘car to help clarify the idea

Consultations are available. See Contact page for more details

Data Management Scenario

The concepts presented here can be applied to any shared storage format:
Files, index cards, boxes, warehouses, and so on

This given example relates to a database on a computer, but in some places it will also include a quick parallel reference to a ‘car component database’ to help clarify the idea. 

The scenario:

You are required to design a database structure that will hold over 400 software files (data), these files relate to 8 different products (user) each of which uses a cross-section of the files for their own needs, each product references about 250 files within the database.

Just to add to the mixture – some products share common files and some files are unique to a product.
This is why there can be over 400 files for products that use only 250 each – some of these files are shared by all products, others are common to some products, and some are used only by one product. Therefore those files not totally common (150+) are ignored by the products that do not require them.

These files must be accessible to a number of different people (user), each of whom could make a change to any file. If the changed file is a file that is shared between products then the other products would need to be informed of the change so they can include the updates into their product (change control).

In this scenario there are two groups of users that must be accommodated. The first are the products that use the files for their software operation, the second are the people who use the files to design the software for the products.

A data management nightmare!

Summarising the objective:

  • To have one set of files within a database structure designed to allow any of the 8 products to use any of the files that it requires.
  • To provide access into the database and to ensure that any updates to a shared file performed by a person must then relate to every product and must therefore then be used by them all.
  • That although some of those files are shared by more than one product, each file must only exist once within the database (critical design point)

Car parallel:

  • You must have a record of all of the components used by the cars placed within a database structure. This must be designed to allow any of the 8 Car types to use any of the components that it requires.
  • You must provide access into the database by those that need to use it, and to ensure that any updates to a shared component performed by a person must then relate to every other car type that uses it, therefore each of the other car types will need to be changed to use the updated part.
  • That although some of those components are shared by more than one car type, each component record must only exist once within the database (critical design point). It may be a case that a car also uses more than one of that component, but the details of the component must be unique and exist only once. (The number that a car type uses is not part of this specific database design)

Database Designing

Whenever a new product is to be designed that has similarities to existing products and you require software to be installed into that product, it is always wise to start with the existing software and use it as the basis for the new product.
For this task, the database that I needed to work with that was used to store the software files and had been in existence for a very long time.

Investigating the current database structure the following were identified:

  • Each product had its own area within the database.
  • Many of the files were duplicated, each area would have some file names common to other areas but they had no connection to them. There was no awareness that these other common files even existed.    
  • Many of the duplicated files had been ‘tweaked’ slightly within their own areas, so although they shared the same name, the file contents did not always match the equivalent file in another area.
  • The structure of the current database did not efficiently reflect the structure that was actually required for the product.
  • About 250 files were used to create the software package for an individual product. These files could vary between products, some shared common names, others were uniquely named for that product.
  • There was obsolete code buried within many of the files, caused by being ‘tweaked’ over the years.
  • There was some unused (obsolete) files lurking in the corners.

For the car design scenario you would need to obtain a list of what components each of the cars currently used and what ones were common between cars and which cars they were. Have some been updated with later versions of a component and others haven’t? Have some been altered in some way? Have some stopped using a component?

1) Informing the current users what you are planning to do

For multi-user databases the most important action at the start of the project is to talk to the other people who would be affected by these changes, to those whose files are stored in the database. From these conversations you can learn what the current problems are with the existing database and also what features they would like to see in the new design.

Warn the car manufacturers that an update to the component database is on the way and get there feedback on current problems! See if they have any suggestions to what they may like to see.

2) Identifying the key (basic) operational categories

The basic categories of the database need to be identified, for this example these categories would relate to the products operational features. They include:

  1. Files for controlling the system – Shared by all. These system control files are responsible for the basic system operation of the products.
  2. Files for hardware component interfacing – Shared between those products who have that specific component in their design
  3. Files for what the products are required to do (functional requirements) – These particular files do the work within the product using the system control files and hardware files to perform the requirements of the product.
    • Every product will have its own selection of functional requirements, each of which will have specific operations that it needs to do.
    • Each functional requirement will form a separate category in the database and will need to reference the collection of files it requires to perform it operations.
  4. Files that are unique to a product – these files only ever relate to the one product. These are often the ‘top level’ files that connect the person with the product, providing the unique interface to the product.

These categories would become part of the fundamental database structure design

For the car component scenario, you would have a list of all of the components every car uses (e.g. Wheels), components that are shared by some and not others (e.g. colour the car comes in) and some unique to a car (e.g. a feature that identifies it to those walking pass).

3) Identifying the commonality of the requirements between the products

Having established the basic categories I turned my attention to the products and started to identify the commonality of the requirements (functional or hardware) between the products and, therefore, what operational categories they had in common with each other. For example which products had a hardware component that was also used on another product.

This task started by identifying the requirements that where common to all of the products, then moved on to identify the requirements that were shared between some of the products (making a note of which of those products shared that requirement). Once that task was completed then the only remaining requirements would be those that were unique to a single product.

The access of the products into the database design has now been identified

For the car component scenario, you would find out if the ‘blue’ colour is common to all cars, shared with other cars or unique to one car.

4) Creating the ideal database layout from the information gathered

Now comes the critical design stage. Ignoring the current database layout it was time to work out what the ideal structure design would be, based upon the information that had been collected.
In a bazaar way this task is almost 3-dimensional in it workings, or multi-layered in two different ways – on the one hand you had the unique operational categories that may or may not be used in a product, and on the other you had the unique products that may or may not share operations.

I choose to design the new database structure around the operational categories, starting with those shared by all products, then those shared by some, finally those operational categories that were unique to a product. NOTE: a unique category may not stay unique if a future product decides to include that operation, but this database design is future proofed for this.

Each different category was given its own area within the database so that the files within it would exist only the once and would not be found anywhere else, thus ensuring that they remained unique and, where shared by products, common to all products.
In this example the database was configured to have one common area for the files that dealt with the hardware components and another common area for the basic underlying system control, before creating specific areas to contain the files that performed the different shared or unique operations of the products.

For the car scenario, on the one hand you would have components for common operations for all types (Operational – engine components, wheels) and, on the other, those components used by a car that is not an operation (Product – a special feature of their range, their logo badge)

5) Bringing the products requirements into the database

Once all of the different operational categories had been assigned their areas within the database, then each product was given its own area within the database, the information in here would provide the details of what operational categories they used, as well as the specifics about the product that may be required to be referenced by operational files.

It would be in this area that you would learn what hardware components it was required to work with, and the different optional operations it would provide as part of its design. 

For the car scenario, each car type would have a list of the details of the operational components it uses (wheels, etc.), as well as specific components it uses (e.g. logo badge)   

6) Making the ‘shared’ files common to all products

This is the most time consuming activity, for you must now enter the files into the database and ensure that ‘one file would fit all’.
Due to the duplication of the so-called common files, each file that was ‘shared’ across products required its contents to be examined to identify the differences between the files of the same name used between different products. Once the differences had been identified then changes to the file would need to be implemented in such a manner that whatever product used that file it would operate as that product had expected it to do within its own area. Great fun!

From a software perspective this would require a lot of extra testing in the software  (‘if – then – else’) and switch statements to be designed into it. The decision making would be defined by the product that is using it (if product A then do this… if product B then do that …).
Obsolete code was also checked for and removed during the process, as were any unused files.

For the car design scenario, the category could be ‘wheels’. Whenever a specific car used that category, a check of the car type would identify which specific wheels were required for that car (if car B then use …, else… ). Cars no longer being produced would be obsolete and all unique components could be removed from it (e.g. their wheel type)  

7) Ensuring that it stays ‘tidy’ when people use it

Once you have your ‘well structured’ database you then need to ensure that it stays ‘tidy’ when it is used by many people, this is known as ‘change control’ and is a large topic in its own right and is covered separately: Change Control

However to give you an idea what this involves for this example I will give a brief description of how it was implemented for this database. This considers just a few points of change (or configuration) control that relates to this example only. 

Change control management for this database

(for full guidance: Change Control)

A requirement for some databases is the need to be able ‘track’ the changes over time, to be able to look back in time to see what the changes had been since then. For a PC database system that stores software files, the implementation of ‘change control’ system is the technique used that allows you to be able to  retrieve the software files that were used for a product at a specific version release in the past, ignoring any changes that had happened since to that file.

This enables you to look at the history of the products software to see how it performed the tasks for a specific version in the past. This requirement is essential if a problem occurs with the product, it enables you to look at the ‘Then and Now’ versions to see where the problem first appeared, to see what change you made to it that introduced the problem.

Archiving and file history details

A PC database should provide the facility that allows the files to be ‘archived’, allowing you to put newer versions of a file into the database and the previous version would still be available if you were to look at the file at the specific time in the past. This is an important requirement for configuration control as you may need to see the ‘history’ of a file so that you can see what the specific ‘configuration’ of the files were at the moment in time you are interested in

Updating the file

When a change occurs to a file, if the file is common to any other product then those products that use that file must update the file within their own product and will need to check what the change is. This is important if it is fixing an issue with the software, for although the other products have not had the problem, the potential for it to appear is still there – they may have simply not been using that part of the software when it is running for it too fail.

 Jenny Maryl ~  Inspiring the Imagination ~ Contact Me


Comments are closed.

%d bloggers like this: