Questions on database design and normalization

[Charles W. Stricklin]

I've run across a design issue that I'm asking for any help and advice
anyone if willing to offer. The easiest way to explain my
question/problem is just to launch into it:

I'm creating a database to keep track of all the ATMs and cash
dispensers we have under contract: where they are, who they belong to,
when they were installed, when they were last serviced, what kind of
hardware they use, how they're configured, etc.

We deal with several different brands, and some brands have several
different "series". (Some do not... yet.) Each series has several
different models.

ATMs from brand to brand and model to model may contain several columns
of information that are the same or similar. For instance, each ATM
regardless of what brand they are, what series they fall into or which
model they are, will fall under a specific service area or belong to a
specific bank. However, printers of bill dispensers for brand X are
different from brand Y. Likewise, if a particular ATM uses one protocol
to connect to a network I'll need to store certain data exclusive to
that protocol, and if they use another protocol I'd need to store a
whole different set of data.

I hope I haven't completely muddied the questions, and like I said
before, any help is appreciated.

Charles

 

[Tim Ferguson]

ATMs from brand to brand and model to model may contain several columns
of information that are the same or similar.

There are broadly two approaches here.

The first is called subtyping, and it involves having a generic table and
several specific tables for the particular machines. This is suitable
when there is a small and fixed number of subtypes with clear differences
(e.g. People can be Customers or Employees or Contacts, etc)

The second is a more generic structure and is adaptable to adding new
types of ATM. It models the different ATMs and their attributes with a
many-to-many relationship (as usual, * is a PK, and + is a FK):

Models(*ModelNum, Series+, ModelName)

Details(*DetailCode, Description)

Attributes(*ModelNum, *DetailCode, Value)

The tough part is getting the right data type for Value: you might resort
to having DateValue, IntegerValue etc, or just code everything into text
values and make sure there is some method for proper validation.

The other sophistication is to control what details apply to each model,
and potentially even valid responses. This is not easy to give a pat
answer to, because it depends on the interaction with the Brand and
Series values too.

Hope that helps


Tim F

 

[Charles W. Stricklin]

There are broadly two approaches here.

The first is called subtyping, and it involves having a generic table and
several specific tables for the particular machines. This is suitable
when there is a small and fixed number of subtypes with clear differences
(e.g. People can be Customers or Employees or Contacts, etc)

The second is a more generic structure and is adaptable to adding new
types of ATM. It models the different ATMs and their attributes with a
many-to-many relationship (as usual, * is a PK, and + is a FK):

Models(*ModelNum, Series+, ModelName)

Details(*DetailCode, Description)

Attributes(*ModelNum, *DetailCode, Value)

The tough part is getting the right data type for Value: you might resort
to having DateValue, IntegerValue etc, or just code everything into text
values and make sure there is some method for proper validation.

The other sophistication is to control what details apply to each model,
and potentially even valid responses. This is not easy to give a pat
answer to, because it depends on the interaction with the Brand and
Series values too.

Hope that helps


Tim F

Ummmm... methinks I'll just break each ATM brand into its own database.

Thanks anyway.

Charles