skycruiser Posted February 19, 2007 Share Posted February 19, 2007 I'm working on my first php/mysql database project and I'm a little bit stuck. There's a big chunk of the project that I'm not sure how to tackle. Hopefully someone here can give me a little advice. Here's what I'm doing. I'm creating a "Documentation Database". Basically a database to record the document names, numbers, revisions and other details about documents we use to design and build the things we make. Also they are tagged by project name (we have several projects going at once). The main table is the project_document table. It has information that describes the document (number, name, release date, author etc) and also the project it belongs to. There is also a column for revision information, basically counts the number of revisions (called Rev A, B etc...). 0 if there are none and each time one is added that number is incremented. There is a separate table with the revision information (release date and author) that references the original document by index. The reason I did this is so if I change the original document description or other information it won't have to be changed for all of the revisions too. OK. So for a particular project there are multiple products created, all similar and with a unique serial number. The primary differences between the is what document revisions may have been used to build them. So I need to figure out away to allow the user to create a new Serial Number item, and then specify which doc revisions were used to build it. I thought I could just create a new column in the project_documents table for each serial number, create a form (filtered to only include docs from that project) with dropdowns to select the applicable revision for each document and record it in that column for that serial number. If I do that I'd have to be able to dynamically create a column (when the user creates a new serial number) and I'd also probably need to initialize it somehow, perferably by copying an existing column (a good starting point woudl be the previously built item). Would this work or is there an easier way? If it would work, can anyone describe how to create an initialized column in the table like that? I'm using Navicat, but I doubt that matters since I need to be able to do it with php. Thanks for any help - Michael Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted February 19, 2007 Share Posted February 19, 2007 You might want to look at how some other people have done what you want...one of the more robust document management systems is knowledge tree... http://www.knowledgetree.com/ They have an open source version that you can download and play with if you like. Quote Link to comment Share on other sites More sharing options...
skycruiser Posted February 19, 2007 Author Share Posted February 19, 2007 Thanks for that link. I've downloaded it and I'll see what I can figure out. So far it looks really complicated though. I've only been working with this for 2 or 3 weeks. Quote Link to comment Share on other sites More sharing options...
Greaser9780 Posted February 19, 2007 Share Posted February 19, 2007 Might be another possible solution. Create a new table PRODUCTS prod_id,serial_number,description,revision_id,project_id also if your revisions table has an id for the revision you could include that id in your products table to reference what revision docs were used. revision_id Ditto with the project table.If it has an id field you could also enter it in products table. project_id Quote Link to comment Share on other sites More sharing options...
skycruiser Posted February 20, 2007 Author Share Posted February 20, 2007 Might be another possible solution. Create a new table PRODUCTS prod_id,serial_number,description,revision_id,project_id also if your revisions table has an id for the revision you could include that id in your products table to reference what revision docs were used. revision_id Ditto with the project table.If it has an id field you could also enter it in products table. project_id Thanks. I'll give this some more thought. The problem I see off-hand is that every document referenced has to be duplicated in the products table for every serial number for that project. A lot of repeated information I guess. But if I can't figure out how to dynamically generate table columns I'll try that. Quote Link to comment Share on other sites More sharing options...
Greaser9780 Posted February 20, 2007 Share Posted February 20, 2007 Do you mean the revision documents? Quote Link to comment Share on other sites More sharing options...
skycruiser Posted February 20, 2007 Author Share Posted February 20, 2007 Lets say there are 3 documents (each with some number of revisions) and 3 products (serial numbers). The PRODUCTS table would have something like this SERIAL# DOC# DOC REVISION 1DOC1 DOC1Rev#forSN1 1DOC2 DOC2Rev#forSN1 1DOC3 DOC3Rev#forSN1 2DOC1 DOC1Rev#forSN2 2DOC2 DOC2Rev#forSn2 2DOC3 DOC3Rev#forSN2 3DOC1 DOC1Rev#forSN3 3DOC2 DOC2Rev#forSN3 3DOC3 DOC3Rev#forSN3 So let's say we throw DOC1 out and replace it with something completely different DOC4. DOC1 has to be deleted (or marked N/A) for all 3 Serial#'s and DOC4 inserted for all 3. Whereas if it were in one table only one row for the document would be added and the appropriate Revision # would be put in the 3 cells for those 3 serial numbers, for those 3 serial number columns. The project could be keyed into column name as well so that would never have to be sorted into a query. I hope this answered your question. If not please clarify and I'll try again. Thanks, MIchael Quote Link to comment Share on other sites More sharing options...
skycruiser Posted February 20, 2007 Author Share Posted February 20, 2007 Anyone know the command for adding a new column to an existing table? I could just do that and initialize it with a simple loop. I've done some searching but must not be doing it right because I come up with INSERT INTO and CREATE help. I don't know the correct terminology. Thanks. Michael Quote Link to comment Share on other sites More sharing options...
skycruiser Posted February 20, 2007 Author Share Posted February 20, 2007 Wish i knew how to edit posts.... I found it - alter table. Michael Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.