Solved

Need help interpreting table schema  (i.e. strvalue, bvalue, dvalue, lvalue)

Posted on 2008-10-01
11
206 Views
Last Modified: 2012-05-05
I recently took over a database and I'm not sure how to interpret the design.  Please see the picture below for a glimpse at two tables.

These tables are linked to other tables such as SiteOffice, Department, HardwareManuf, HardwareCategory, HardwareItem

I could think of easier ways to create tables that would make the design easier to follow and update.  I'm wondering what it is I'm missing.  For example,  I'm wondering why the 'strvalue' field is used to hold more than just the service tag of a workstation.  It also can be used to hold an asset 'group' number (i.e. workstation, keyboard, monitor) ... and it can be used to hold an asset barcode.  Any of these three could be referenced by the 'strvalue' field if the following statement were used:

SELECT F.uid As Group_ID, AF.StrValue, F.FieldType, F.Label
FROM AssetFields AF INNER JOIN HWFields F ON AF.FieldUID = F.UID
WHERE AF.AssetUID = 'XYZ123'
AND F.Label = 'Group'

The above query could also end with either of these two:

1)  AND F.Label = 'Barcode'
2)  AND F.Label = 'Servicetag'

Thus, since the 'strvalue' field accepts nulls as seen in the diagram below, isn't it safe to assume the only catch or filter on unique entry would be the user application that accepts new assets?

Again, what am I missing?  Thanks!

table-design.JPG
0
Comment
Question by:John500
  • 8
  • 3
11 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22615085
AssetField appears to be a cross-reference table between the ASSET table and the FIELD table.

Evidently, the field can have different types ... and the strValue is the key to determining which asset type it is.

It's not a very nice design, but I must admit to having done something similar before.  I had an Address table that had an ObjectID and an ObjectType.  The ObjectType was the key to tell whether the address was for a Customer, a Vendor, etc.

Without a more thorough look at the data, I can't say for sure, but that appears to be what this DB's designer is doing.
0
 

Author Comment

by:John500
ID: 22615240
Ok.  But as far as a unique entry for such things as a workstation service tag, do you agree there is no way there could be protection against duplicate entries?

I have 10 duplicates so I guess I could say case closed -  but again, in theory, is there any way duplicates would be even be difficult to input?

Thanks
0
 

Author Comment

by:John500
ID: 22615285
Here is the FieldValues table you mentioned:
table-design2.JPG
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:John500
ID: 22615392
Putting the question a different way, would you agree that just because the 'uid' for FieldValues is a unique identifier, this uid is not associated to the 'strvalue' for constraint purposes, right?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22615461
Well, this design does mean that foreign keys can't really be used in this area.

You might try some unique constraints, though.  Would it be accurate to say that, in the AssetFields table, AssetUID, FieldUID, and strValue should form a unique combination?  Also that none of the 3 fields should ever be NULL?

It looks that way from here ...

If so, I would make those 3 fields NOT NULL, then create a Unique constraint on them.  After deleting the duplicate data, that is.  A delete on a self-join should do that ... can write it if you need.
0
 

Author Comment

by:John500
ID: 22615590
>> A delete on a self-join should do that ... can write it if you need.

Sure, that would be great.  Thanks!
0
 

Author Comment

by:John500
ID: 22615681
Here are the results for a query that demonstrates the nulls already entered:
strValue-results2.JPG
0
 

Author Comment

by:John500
ID: 22615783
>>  Would it be accurate to say that, in the AssetFields table, AssetUID, FieldUID, and strValue should form a unique combination?

Could you pose any argument as to why they might not?  I'm trying to envision the design but sometimes it's hard to see what was intended.  I really have nobody to advise me on this because the previous developer is gone.  I do know the types of assets which are tracked - basically soup to nuts (computers to tables & microwaves).

Given this information, do you see any reason why these columns would have allowed nulls on purpose?

Thanks
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22615964
>>Given this information, do you see any reason why these columns would have allowed nulls on purpose?

No, I don't.

For those NULL strValue's ... Are the AssetField records completely missing?  Or do the AssetField records exist with NULL's in them?

As far as the delete on self-join to clean up the AssetField table, please try this (after creating a backup in case something's goofy):

Delete From AF2
From AssetFields AF1 Inner Join AssetFields AF2 ON 
  AF1.UID < AF2.UID AND AF1.AssetUID = AF2.AssetUID AND AF1.FieldUID = AF2.fieldUID And IsNull(AF1.strValue,'') = IsNull(AF2.strValue,'')

Open in new window

0
 

Author Comment

by:John500
ID: 22616601
>>  For those NULL strValue's ... Are the AssetField records completely missing?  Or do the AssetField records exist with NULL's in them?

I interpret those results as saying there are:

43       records all null except for the information given
1         records all null except for the information given
18       records all null except for the information given
106     records all null except for the information given

In other words, I think the application that is responsible for importing assets to the database was executing transactions with null values where seen.  Why the application didn't have any values, I'll never know.

Is there some associated table that pops out in your mind that I should be looking in?  The associated tables to me would be CatHWItem & CATHWType  (category hardware), but the data they contain is also seen in the screen shot - right?

Thanks!

0
 

Author Closing Comment

by:John500
ID: 31501995
Thanks again for the help.  The thoughts you discussed were certainly relevant!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - Select rows into colums 4 21
SSRS 2013 - Creating a summarized report 19 32
SQL Server 2012 r2 - Sum totals 2 21
SYbase 4 24
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now