noob general design advice

[skycruiser]

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

[hitman6003]

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.

[skycruiser]

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.

 

[Greaser9780]

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

 

[skycruiser]

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.   

[Greaser9780]

Do you mean the revision documents?

 

[skycruiser]

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

[skycruiser]

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

[skycruiser]

Wish i knew how to edit posts....

 

I found it - alter table.

Michael