Identifying the Problem and Designing the Solution - Beginning Oracle Application Express 4.2 - page 43

Although you could go a lot further, these requirements form the basis of a pretty
complete help-desk system. You can always add functionality to it later when you have
a better understanding of what else the users and the company might need.

Extrapolating to a Database Design

Having stated the requirements, you can begin to extrapolate the database objects you
need to create to store the data. If you're new to database design, here's a quick trick to
help identify the entities for which you need to build tables: go back through your re-
quirements and look for concrete nouns that represent the highest-level objects you
need to track. As you find these nouns, try to identify if they're actually at the highest
level or if they're merely attributes of something bigger.
If you follow the described process with your brief requirement specification, the
nouns USER and TICKET jump out as being the two main things you want to track.
It's tempting to split users into two different sets—technicians and end users—but the
type of user is merely an attribute of a user.
An object that is a little harder to identify is TICKET DETAIL. It's completely valid
to think that this would merely be an attribute of a TICKET; however, the clue comes
in the fact that you can't concretely identify how many TICKET DETAIL entries there
will be for any given TICKET. The fact that the number is unknown indicates that you
should create a table that is a child of the TICKET entity called TICKET DETAIL.
This way, you can enter as many detail records as you need.
So, you've identified three major entities: USERs, TICKETs, and TICKET
DETAILs. You now need to think about the attributes of each of these entities and what
type of data they will hold. Searching back through the statement of requirements, talk-
ing to the technicians about what they track today, and thinking about what types of
things you'd want to be able to track during the process of solving a problem, you can
identify a number of attributes about your objects. Tables 3-1 through 3-3 show these
attributes.

Table 3-1 . USER Attributes

Attribute Name Type of Data Comment

User ID Text A unique ID for each user

User Name Text A login id for each user

Password Text The password used to log in to the system

Table 3-2. TICKET Attributes