0

I want to implement an extension to my following table

Object table == building in generall

+--------+-----------+------+--------------+-----------+-------+------+
| obj_id | obj_ident | name | FKsupervisor | FKAddress | Phone | Fax  |
+--------+-----------+------+--------------+-----------+-------+------+
|      1 | ABC-123   | Test |            1 |         1 |  0000 | 0000 |
+--------+-----------+------+--------------+-----------+-------+------+

The object(building) need's to have also some extra field's like electrical review/sctructural review and several other types. On top of that they can be a yearly based review or 5 years review. The question is how to make a quite simple and scalable schema for the table that will keep the reviews. Reviews should cosist of the following fields as base :: [ each building can have multiple reviews ]

* added(datetime), a scan(pdf<varchar> to patch), 
* obviously the link to object(building), 
* possible the type of valid review lenght i.e 1 year/5 years (datetime) [ expire date ]

Looking forward for some ideas. Thanks.

1

I would consider adding 2 more tables. "Reviews" would contain the review columns itself, DateAdded, ExpiryDate etc, and a ReviewID. Then I'd have a second (junction) table ObjectReview which links buildings to Reviews, and would contain obj_id and ReviewID to tie reviews to buildings.

One important point - it's not clear from your question - but you call one column a scan ("pdf"), I would most strongly advise against storing PDFs or other BLOBs in a database. Leave them in the filesystem where they belong and store only a pointer to that file in the database. I hope that's what you meant!

  • What I meant with a pdf is that I only store the path to the file in a var_dump, is that bad too? Well your answer was what I would try to implement also, good that I have a confirmation on that one – Kavvson Empcraft Apr 21 '16 at 13:59
  • No that's not bad, storing the path in the table is fine. – Thomas Pullen Apr 21 '16 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.