I need to develop a database-based application that offers its users an easy-to-use UI to extend the DB as well as view and edit its contents.
Its users will not be IT-savvy. They're the kind used to filling their data into several excel sheets.
Up to now, those excel data sheets have been manually edited, causing consistency errors. Also, many sheets relate to data in other sheets, creating a kind of relational database, managed by hand.
That company is now asking me to provide them with an application that replaces Excel and instead manages the data better, with the following advantages:
- references between tables are managed in a way that prevents semantic errors
- 1-to-N relations can be seen from both ends (i.e. an item's list of refs as well as who references an item)
- search across all tables
- users can add new fields to a table, and also create new tables
- optional: multi-client capable
Now, as an experienced C programmer, I could write this all by hand. The DB management is easy enough. But then I'd have to implement editors, generic form generators, etc, all dynamic. I just suspect that there are 1000s of solutions for this kind of use already that would save me some time to get this little project done.
I'm just not having any clue of the whole DB market as this is outside my usual field of work (I'm more of a system programmer). I know how to design and use SQL-DBs. The question here is more about providing a frontend.
Other requirements and factors:
- If software purchases are necessary, a few $1000s are OK
- Should be usable on Windows and Mac OS X
- The DB won't be large, it will be less than 10000 items in the DB, with less than 50 tables. No worries about high-performance here.
E.g, I wonder if there is a web-server-based solution that performs all these operations via a web browser? Maybe a PHP based system using something like MySQL as the DB backend and generating dynamic html pages from the tables, plus adding function for search, etc?
Or is there a IDE that provides a visual DB and forms designer, maybe with a simple programming language that covers nothing but a few functions for string comparison etc. to provide basic semantic checking? And which runs an http server or provides x-platform clients?
As you see, the goal here is to make this a quick and cheap solution that is significantly better than manual excel use but not too complicated as there's a limit in what the company wants to spend in return for easier use.
Any cool ideas?
-- update after 3 replies --
OK, I've gotten quite a few pointers now. It's a lot of things to check out.
I wonder if someone can help me limit the choices a bit. One important request is that the UI I design for the end users still provides ways to add new tables and define their fields (in a limited range I define), as well as add new fields to existing tables.
Here's a practical example of the data we're talking about:
Think of a car parts supplier. They offer seats for various cars (which they do not sell), and adapters for all those kinds of cars.
So they maintain a database of their seats with their adapting parts and other accessories.
- Basically, there's one initial table of cars they know of.
- Then, for each seat there's a list of cars where that seat can be put into.
- Lastly, there's those adapters which apply to certain cars and seats
Now, the users shall be able to add new rows to the seat table, whenever they create a new seat. That's just a simple "table add row" operation, with a rather static entry form.
If there's a new car coming out and they make a fitting adapter or find out that an existing adapter even already fits, they add a new row the cars table and then have to go thru all adapters and seats and mark those that fit this new car.
Eventually, they may decide to also make steering wheels. That requires them to add a new table. I want to provide the UI for that, too, which shall adhere to the same semantics as the seats and parts tables.
Lastly, they may decide that they have to add a new field (column) to their seats to add more information to the customers or service people.
In the end, this database infomation shall be transitioned into a format suitable for printing a catalog. This part I will implement separately, so this is not needed to be part of the tool I'm seeking here.
The users may want to see all relationships from both ends: e.g. (a) which seats can be used in a particular car, and (b) which cars does a particular seat support?
So, does anyone know which DB tools can help me design the GUI (edit forms, reports, search forms)? I.e, the forms may not be static, but must be able to automatically show new fields when added to the DB schema.