Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain a collection of assets that are registered to one or more users. I have never worked on any data outside of SQLite and even then I have only made a few very small databases. As such I am not sure that my I handling this design correctly. As I expand on my design, it is beginning to look like a mess. My questions are:

Am I designing this database in too much of an ObjectOriented fashion? As a java programmer, this is just how it makes sense to design the database.

Is there any way to make the database look less like a mess?

Am I even on the correct track?

For reference here is an earlier more easy to read protoype of the database design. No typings have been assigned yet, sorry.

What you call messy is actually normalization which is ordinarily a good thing.
–
JNK♦Jul 24 '12 at 16:57

@JNK, Ok. So this much fragmentation is a good thing. What would be an extraordinary situation?
–
AedonEtLIRAJul 24 '12 at 17:01

It depends on your use case. If you will be inserting/updating a lot, normalization can be preferred because you are locking/updating less data at a time. In a read-heavy environment with static data, denormalized data can be preferred because it simplifies queries and can perform better for reads.
–
JNK♦Jul 24 '12 at 17:04

1

Some questions, though - it looks like Asset/Publication/Barcode are ALWAYS together except in one instance. Are the barcode and publication a property of the asset?
–
JNK♦Jul 24 '12 at 17:07

1

Looks like this model could benefit from some parent/child type of relationships, but otherwise it seems totally reasonable.
–
Jon SeigelJul 24 '12 at 17:15

1 Answer
1

This is more of a comment, but since this is a lengthy response that's worth mentioning, I'm posting it as an answer.

Your database looks sufficient for your needs. (Well, as best can be assumed from the diagram.) I assume that the UserAccount table has a one-to-many relationship between the four tables Vehicle, Book, VideoGame and Movie (from here on, the V-B-V-M tables.) Next, I assume that the Asset table has a one-to-one relationship with each link between the V-B-V-M table entries. With this information, I would recommend moving the UserAccount_id from each V-B-V-M table, to the AssetTable.

Next, it seems that Asset is always associated with one of the four V-B-V-M tables. Considering this type of setup, I would recommend creating an additional table-- AssetType.

Next, update the Asset table and each V-B-V-M tables to include a AssetType_ID field. the AssetType table should include one entry, for each type of asset. In this case, Vehicle, Book, Video Game and Movie, each with a unique description and ID.

You may want to do this for a couple reasons-- First, you can now query the Asset table and discern what type of asset is stored in a specific record, without having to link the data to your four other V-B-V-M tables. This will speed up your querying of the data, when all of the information from your four tables isn't needed. Second, this also provides an easy means of linking in a textual description of the type of asset associated with the Asset entry. Again, without having to link to the V-B-V-M tables to figure that out.

Last, you may want to consider handling the barcode data differently. If barcode is a simple look-up, you can add it to the asset table. However, if barcode values have to be unique, keeping it in a secondary table is necessary because you'd have to put a constraint on the database. You could do this if it was integrated in the Asset table, however, your vehicle data would cause problems because vehicles do not have barcodes. To my knowledge most, if not all, major database engines won't allow you to define a unique field, that also allows null values because, then, the data is no longer unique.

You might be inclined to think that you could combine the Barcode field with the VIN. Since VINs are unique and Barcodes are unique, and neither should be the same, why can't they be combined into one field on the Asset table, which allow you to drop the additional Barcode table. I wouldn't recommend this because I think it gives the data less "focus".

Yes, it is a form of unique object ID but barcodes are barcodes and VINs are VINs-- the concept behind both is completely different and consolidating the two can become confusing with time. Furthermore, if you expand your project in the future, it could cause collisions. Technically, I don't see a big problem with that (in your case) but I'd still highly recommend avoiding that option.

Also, I would also add the assetType_id to the Barcode table, for the same reasons you added it to the Asset table.

Thank you for the lengthy and complete answer. I acutally had just done most of that right before your answer posted. After the comments and consulting the hierarchical wiki again, it seemed like the best and most logical idea. I, however, did not think of adding the asset_type. That also makes an abundant amount of sense.
–
AedonEtLIRAJul 24 '12 at 17:43

@AedonEtLIRA: Yeah, I hadn't seen your update while typing up the answer. I would recommend moving the Barcode data to the Publication table, unless you expect Barcode to account for multiple instances of a publication. Is the barcode specific to a single object, or is this something like an ISDN?
–
RLHJul 24 '12 at 17:45

I have considered moving Barcode to publication. The more I think about it, the more it makes sense to be in there. A Barcode is not guaranteed to be unique. Someone could have multiple instances of an asset. Where would we be if (s/h/it) wanted to keep track of each? Then again, I could have a field in the Asset table for quantity. Thanks again.
–
AedonEtLIRAJul 24 '12 at 17:52