Q
Problem solve Get help with specific problems with your technologies, process and projects.

Database returning duplicate records

I have a problem getting correct results from my database. My application is completed about 80%, but now when I want to make my reports I find that my database design is not correct (returns duplicate records).

I have a big problem getting correct results from my database. My application is completed about 80%, but now when I want to make my reports I find that my database design is not correct (returns duplicate records). I want to save all information about hardware in an office (like RAM, hard drive, monitor, etc.) and then in my application select all hardware that one computer needs. So in this way I can save all information about each computer and I know how much free hardware exists. My database consists of one table as master (ID, computer name, computer user, etc.) and one table for each type of hardware as detail (all of them consist of the "ID" that is in the master table).
Many designs incorporate a table commonly referred to as a "crosswalk" or detail table. Let me give you an example. Let's suppose I have a table of all desktops in my office. The DESKTOP table can have the following columns:

ID
NAME
USER
LOCATION

The information in the DESKTOP table stores one row for each desktop in the office. So the contents might look like the following:

ID  NAME   USER  LOCATION
1   vader   bob   Rm 100
2   kenobi  sue   Rm 101
3   sidius  john  Rm 101

Now I'll have another table with all of my possible components of those desktops. Such a COMPONENT table might look like the following:

ID  TYPE       VALUE
1   Harddrive  100GB
2   Harddrive  200GB
3   Memory     1GB
4   Memory     512MB
5   Memory     2GB

So now all I need is a table in the middle to make a connection between the desktops and their components. Such a table (we'll call it DESKTOP_COMPONENTS) might look like the following:

DESKTOP_ID  COMPONENT_ID
1           1
1           3
2           1
2           4
3           2
3           5

Joining all three tables with the following query should reveal the components of each desktop:

SELECT d.name,c.type, c.value
FROM desktop d, desktop_components dc, components c WHERE d.id = dc.desktop_id
  AND dc.component_id = c.id;

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close