Asset tagging and abstract objects

[neo651]

I'm designing a database to track company objects. Most of the objects are standard assets like desktops, laptops, cell phones, etc. But there are also more abstract objects like applications, licenses and offices. It is a non-negotiable requirement that all of these objects be in the same table. There are numerous instances where one of the properties of an object can be many different object types. For example, we track out software licenses and we need to track where that license was deployed. A piece of software can be used on a desktop, a laptop, a server or a mobile device, but we could also deploy it on one of our virtual servers, which is an abstract object. There are numerous instances of situations just like this.



So the issue, then, is that we want to do asset tagging, but since we have abstract objects mixed into our table that makes for hundreds of objects that would be assigned an Asset ID but are not, in fact, a physical asset that can be tagged. This seems like bad practice and also would waste money since we plan on getting our asset tages preprinted, many of them would just get thrown away.

From a design standpoint, how do I work around this problem while maintaining the ability to reference real and abstract objects in the same column.

Thank you.

[help_me_with_access]

there isn't a *standard* way to design a database anymore. but according to the academic theorists, there is 3 levels of standardization. but that means nothing.

so if I understand you, you want to simply track all of your assets, regardless if they are tangible or not. correct?

if that's all you need, the app wouldn't be complicated so the architecture aspect of it would be a moot point to say the least.

[neo651]

I need to track various object, tangible or not, I need the tangible objects to have a separate ID number from the intangible objects so that I can do proper asset tagging. But I need to be able to reference both of them in a single column.

[help_me_with_access]

that shouldn't be a problem then. you'll just have to customize your ident field, kind of like acc using an autonumber.

there are plenty of resources to customize. vb code for one. vb is a good language, but resources are limited when compared to others. however, in your case it works fine as it has everything you need.

want help with that? can you show me what the table looks like now? have you started on it ?

[neo651]

If you could help that would be great. As far as programming I only know a little java but virtually no vb. This is my basic table structure. I call them Entities since they aren't all physical assets. You'll see I have recursive relationships for both the Entities and EntityClasses.

tblEntityClasses
EntityClassID - pk
EntityClassName

tbl_SelfJoin_EntityClasses
EntityClass_SelfJoinID - pk
EntityClassID -fk (1-to-many with tblEntityClasses.EntityClassID)
Assc_EntityClassID -fk (1-to-many with tblEntityClasses.EntityClassID)

tblEntities
EntityID - pk
EntityClassID - fk (1-to-many with tblEntityClasses.EntityClassID)

tbl_SelfJoin_Entities
Entity_SelfJoinID - pk
EntityID - fk (1-to-many with tblEntities.EntityID)
Assc_EntityID - fk (1-to-many with tblEntities.EntityID)


P.S. Those aren't spaces in the table names, they're underscores that are just underlined into invisibility.

[help_me_with_access]

well first of all, your duplicate tables (or "recursive" relationships" as you're calling them) should not be there. access is not meant for this, plus I've never seen this done. they simply look like duplicate data tables to me. no need.

that's good you posted the table structure actually. I might have said this before and might not have, but see in a situation like this there are a million and one ways to do this. so what's really import is: who's needing the data on the front end side? the user side, that is?

I don't know what asset tagging means, but I've seen it a few times. you said something about printing?

do you have any data stored yet? if you don't, you need a brand new table set for your file. that table set is not good. I'll give you one to work with, but first tell me how far along you already are...

[neo651]

The self-join tables are necessary, they're not duplicate data tables. They are how I associate EntityClasses and Entities to themselves (i.e. Associate a monitor to a computer, user to a cell phone, etc.).

On the front end this intended to be used by our helpdesk technicians, but in the future we may extend certain functionality out to general employees.

Asset tagging is when your company's physical property is a assigned a unique ID, you print the ID out on a sticker (usually with a barcode) and slap it on that piece of property.

No, no data stored yet. I'm still in the design phase so my tables are all empty other than some test data.

[help_me_with_access]

The self-join tables are necessary, they're not duplicate data tables. They are how I associate EntityClasses and Entities to themselves (i.e. Associate a monitor to a computer, user to a cell phone, etc.).
.

actaully, NO. this is not the way to approach it. plus, per your words:

But there are also more abstract objects like applications, licenses and offices. It is a non-negotiable requirement that all of these objects be in the same table

that apparent "order" you got should be right on with what you need to do anyway. the join tables are not necessary and are bad practice, believe it or not.

what you should do is this:


tbl1) ASSETS
asset ID (autonum?) (pk)
asset name
asset type (fk tbl3)
...
...

tbl2) ASSET ASSOCIATIONS
asset id (fk tbl1)
association id (fk tbl4)
association description (e.g. => details of person using?)
...
...

tbl3) ASSET TYPES
id (pk)
type name (e.g. => software, hardware, communicae, etc...)
tangible?? (boolean val)

tbl4) ASSOCIATION TYPES
id (pk)
type name (e.g. => software, hardware, communicae, etc...)
tangible?? (boolean val)


now you see, in theory this is how things are supposed to be done. but this is one way. the general purpose of a database's existence is to dimensionalize data. Otherwise all we would have to keep track of data lists on Earth would be just that, LISTS. on paper, whatever, but line by line... a good database is exactly what I've shown you above. You can do what you want with it.

But the important part to understand about database programs is that associated data blocks are NOT supposed to be kept close to each other (relative to the software objects they are stored in). That's why database engines exist, their jos is to pull data from just about anywhere and display it to YOU as if it really were that simple.

here's a great example of how this works in a server farm environment using HUGE amounts of databases:

here's the page URL for our football team around here: http://www.facebook.com/IowaHawkeyeFootball, and here's a picture from that profile page: http://www.facebook.com/photo.php?fb...50615861821574. but those 2 pages come from different tables in the same database. Or rather, they probably come from different databases altogether because facebook has so many server farms that it needs in order to power its operations. that's exactly what I showed you above, hugely amplified. by the way,

and for your entertainment and extra knowledge => profile pages on facebook are supposed to look like this is their simplest form without SEO changing the actual URL: http://www.facebook.com/profile.php?id=1482992. (and yes I know the page is not found. not a good idea to pick on someone you don't know!). But here's an example too, of what I believe facebook does, in order to hide the ID's of the database records for peoples' profiles: http://www.facebook.com/brandie.parsons.333. Obviously that link isn't found either as it's not supposed to be, but what they probably do is append integers to FIRSTNAME.LASTNAME as an SEO technique, also in order to hide ID numbers of profile records. More than likely they do this for 2 reasons, 1 of course being security and other probably because there is no doubt more than 1 person on Earth with the name Brandie Parsons exists. with 8+billion people lingering around, I would at least hope so!

But aside from my little entertainment section there, what I wanted to show you is how the big boys do it. same concept is applied everywhere in the corporate world, just different flavors of it. trust them. Obviously they know what they're doing.

[neo651]

I'm sorry, I don't follow. I don't understand what the objects in tbl4 are and I don't see where the association between assets is made.

[help_me_with_access]

no problem bud. why don't you upload your file here to the board and I'll show you an example of what I'm saying. deal? I'll give you an example in a small file and you can start your expansion from there.

further deal?