[RESOLVED] Auto generate web form

tnylsej

Hi all..

I am to do a hardware management system. The hardware are divided into various categories i.e. printer, scanner, server etc. Each category has different fields. Hence I've a table for each category. The user can add or update the various tables via the web forms.

The system allows the user to add new category too. Upon addition of new category, i will have a new table created in the database and a new web form for adding and updating the hardware for tt category.

My system should allow user to select from existing fields from other category tables to form the table of the new category. In addition, it will auto generate a webform using the selected fields with their data type and validation.

Anybody knows how enable user to select from existing fields of other tables to form a new table and to generate such form?

I don't have a table to store all existing fields. Is it necessary to have this table? If yes, what should I include in this table to ensure that the data type and validation of the field are being applied to the new category too.

Thanks

etully

Creating new tables dynamically isn't the right approach. You can do this by storing all the data in 5-6 tables.

You will need one table to keep track of the category names.
One to store product names. (For example, HP Laserjet 5m)
One to keep track of the attributes connected with each category.
One to keep track of the individual pieces of hardware and map them to category types.
You might want one to store product descriptions (so that you don't need to enter the DPI, weight, RAM size and dimensions for a HP laserjet 5m printer over and over again).

One to keep a list of options for select fields. (for example, if a printer is to fall into one of 3 types (laser, ink jet, or dot matrix), you don't want the user to type the name because then it's not searchable since some people will type "lazer". So imagine that attribute 17 describes printer types. In the attribute_options table, you would have these three rows: 17,"laser" and 17,"ink jet" and 17,"dot matrix". This way, when the web form displays and sees that printers include attribute 17, and attribute 17 is a select field, it knows to look in the attribute_options table to find the possible options and it makes the appropriate drop down menu.

And finally, you'll need one table to actually store the data for each piece of hardware. For example, assume that Bob and Sue are issued identical HP laserjet 5M printers. You will need to add two new items to the items table. Once you select that they are "printers", you will show a list of printer types. The user will select "HP Laserjet 5m" so that the system already knows DPI, weight, .RAM size, etc). Then the user will see the fields that are appropriate for a printer (where it's kept in the building, serial number, when it was purchased). If it had been a server, then a different list of questions would have been displayed (depending on what you put in the category_attributes_xref table for servers). For example, if all the HP 5M's that you buy are "Laser printers", then there's no need to store they type "Laser Printer" for each bob and sue's printer. But if each Dell server you buy has a different sized hard drive or RAM size, or video card, then you will need to store that data on a per item basis instead of a per product basis.

Does this all seem hard? Yes. Here's the payoff: (1) It's scaleable. It doesn't matter how many products or how many categories you have... you write it once and you never need to work at the database level again. All your tables are created and optimized and you don't need to deal with the optimization headaches that come with having tons of tables that you didn't even create. (2) You write one insert statement and one select statement and it works for every product and every category that exists now and in the future. If you have the system creating new tables for you, then you will spend years maintaining this boondoggle. And worse, when you quit because you just can't face going to work to deal with the nightmare that you've created, the new guy who has to maintain it will say nasty things about you as they build a new system from scratch.

Is my way harder and more complicated? Yes. If you get good at this type of system, you will get big bucks for being the guy who knows how to build the database applications correctly.

tnylsej

Thanks for your reply

Yes, it seems complicated and hard to me. >.< Actually the method stated in my first post are suggested by my supervisor (also the client). I'm still thinking of a simpler way to do it as I'm not very expertised in php and database design. I don't really understand your methods. I was wondering if a new category is added, then new attributes must be assigned to it, how is my user going to define the attributes to the new category and add values to those attributes via the web front end. The attribute values (eg. serial number, model etc.) are different for the hardware within the category too, how am I suppose to assign the value to each attribute. Sorry for not being able to understand ur method.. can u gimme an example of how the database shd be and how the things will work with the following:

Current Categories: Printer, Scanner, HDD, Server and UPS

By the way, each category has their own naming convention (refer to thread)

Printer attributes:
1. PartNo (Unique identifier)
2. IP Address
3. Type ( Dot matrix or Laser)
4. Setup (Local, local/shared or network shared)
5. Brand
6. Model
7. Serial Number
8. Purchase date
9. Warranty (date which the warranty ends)
10. Total testers (total number of testers attached to it)
11. Tester ID(s) (all the tester IDs that are connected to the printer, user gets to select the IDs from a list)
12. Remarks
13. Vendor
14. Currency
15. Cost
16. Status

Server attributes:
1. PartNo
2. Host Name
3. Native Name
4. Purchase Date
5. IP Address
6. Operating System
7. OS Version
8. Brand
9. Model
10. Serial Number
11. CPU Speed
12. Memory Size
13. Mount Name
14. Storage Size
15. RAID System
16. Location
17. Vendor
18. Currency
19. Cost
20. Role ( Function of server)
21. Description (service responsibilites)
22. Remarks
23. Status

I shall just list the attributes for 2 categories else the post will get too lengthy =X

Your help is greatly appreciated.

tnylsej

Hi, me again hehhe

I think I've kind of comprehend ur method =D

I shall go and try it out.. thanks so much! =D

etully

I'm really happy to hear that you're going to give it a try. It's worth the effort because it will help you stretch as a programmer. I honestly didn't expect you to give it a shot. I was hoping at the very least that I would scare you enough into hiring a consultant and she would program it roughly as I outlined above so that you wouldn't have years of headaches.

If you are going to do this, draft it out on paper before you start programming. Choose exactly what fields each table should have and name the tables carefully.

Remember that there are two kinds of data about the hardware. One type is true for every HP Laserjet 5m. (For example, they all have 600 dpi. They are all b/w. They are all laser printers.) So there's no need for the user to have to enter that information every time the company buys a new 5m for an employee. And the other type of data is data that is unique to Bob's 5m. For example, Date of purchase, SN, and which floor and desk number the hardware is located. So you should have two tables. One is for the product information (a 5m is a laser printer) and the other for the individual item information (date of purchase, etc).

This is a big project - plan well. It's not something that I can coach in a online forum. You should do the work but have someone who has done this before nearby to watch your progress.