There are several physical (building) archives for papers. They are located in different cities. Each archive has several cabinets. In each room there are several cabinets (numbered) with several shelves. It is necessary to make an inventory of the location of documents in the archives.

Advise how to be:
1. separate tables for Archives, Rooms, Cabinets, shelves.
2. A separate table for the archive and a separate table Room, closet, shelf?
3. One table for everything?

Approximately 150,000 - 200,000 records in the database (ie, there are so many documents) are assumed

  • Why it is impossible to make a table document in which the required fields will be: location, office number, shelf number? Why do you want to complicate the data structure? - spectre_it
  • I think you can. option 4 - MaximK

1 answer 1

Cabinets and shelves hardly differ from each other. Therefore, they probably can not be normalized. The building has an address, it is linked to the city, the room can be located on a certain floor, it may have an access level, you can create separate tables for buildings and rooms.

In any case, you will have an end-to-end table of documents, as it is developed, pay attention to duplicate string values ​​- if they appear, select them into a separate table with decryption, and leave only numeric foreign keys in the end-to-end table. The smaller your table will be in size, the faster requests will be executed to it.

  • And if documents of several types and they have both common fields, and characteristic only for a certain type. What is there to do? - MaximK
  • 2
    @MaximK The number of document types is predefined? Will not change as the operation of the system? If so, then it is possible to bring out non-coinciding fields in a separate table by the type of 1: 1 connection and connect it with the help of LEFT JOIN, if there is no connection, then NULLs will be returned instead of additional fields. - cheops