So I want to dev a database for my work, its kinda a side project and something we have needed for a while now. A hybrid Inventory/Database program, we have tried many programs but none really meet our needs. Here are my goals and I would like your opinion on how to accomplish them.
This database must have the following:
Nested categories (e.g. Shapes>Squares>Blue Squares>Item: Blue square #12)
Be able to hold hundreds of items
Have dynamic fields (SN, Password, Item type, Phone number, ect... could all be needed fields but I don't want each item to have every possible field type)
Be easy to use
Be accessible over WAN from other simultaneous clients (2-6). (it can run on our servers)
Be exportable to an excel file
I would like it to be able to do the following:
Offline work (at least read only)
Sync with Google Docs (for back up or just use google doc as the database itself)
Be able to create scan-able bar codes (and read them duh...(I'm thinking 3rd party driver that returns item numbers to my program, but I need to interface to it.)
Have pictures link-able to items (online lib and database holds links?)
Have an android app as a client with full control (once I get the database working I think I can handle the integration.)
I have basic Java experience and I like to learn and I do it fast. Like I said its a side project, I know it will take me a long while but thats fine. Maybe we never even use it but at least I will have learned more about Java and have fun while I'm at it.
Just looking for some starting points, here is what i have in mind, feel free to tell me what you think.
thinking mySQL or google docs for the database - but idk if it supports nested categories or if it can even handle what I'm throwing at it....lol
Ah! My post appears! Sweet. Anyway I wanted to add that I am using eclipse to code. Anyway like I said just looking for points in the right direction. Links to good code examples would be great too, I found a few via google that im working with right now.
So I just want to be sure my ducks are in a row before I do a lot of work and find out I have to redo it. :-)
That's a cool project! I did one similiar for my dad's company and it allowed them to push their productivity to take on much more business.
Does GoogleDocs have a database model??
From what you described, nested tables can easily be achieved with SQL by creating code tables for each category and referencing the primary key of the specific code in the table above it, or listing out the entire tree. It does produce a lot of overhead of extra fields though, but it can allow you to not put them into separate tables and distinguish entries by queries not tables.
Another approach that you should look into is a DOM (Document Object Model) database. OrientDB is a lightweight, open source database programmed in Java which stores information you give it in it's existing form and can be retreived without manipulation at a later point. SQL Databases are a classic example of boxing an unboxing. Say you had a Customer class with a lot of fields in a database, well you would have to take all those fields and individually put them into a database with parameters. When you retrieve them, you would have to reset all of the fields. With a DOM database, you just say "this object here, retrieve this object."
A DOM based database would also solve your problem of not wanting to store unused fields. Of course you could also just allow everything to be null.