2. PHPRunner General questions
  3. Planning for a big database - One Table or Multiple Tables
This topic is locked

Planning for a big database - One Table or Multiple Tables

 		11/21/2013 12:32:46 AM
PHPRunner General questions
D
 DUKE author

Hi Guys,
I am working on a big database for a client. At this stage we have only two main tables : Projects and Clients. "Projects" is pretty straight forward with basically just contact details.
The Personnel table is a problem. The table contains about 100 fields under 9 different headings.
My client expects the database to grow to 30 000+ records.
What will be the best way to build this database?
(Option1)

One "Personnel" table with all 100 fields
(Option2) - This is the one I am thinking of.

Split the personnel table into 9 separate tables
Which one of these two options will give us the best performance if the database gets big (30000+ records)
Requirements:
Initially we will need to import data into the Personnel table from excel. If all 100 fields are in one table, this should be easier. With the fields split into multiple tables, we will have to do multiple imports.
Sergey Kornilov admin 11/21/2013

In general you should not worry about this that much. Database servers are pretty smart from the point of storing and retrieving data located in different tables or in different records.
If all 100 fields are in fact compose a single record and there is no duplicate data - you should keep them as a single table. Splitting it into several tables just for the sake of having less fields in each tables introduces many potential data integrity issues. On the other side - working with table containing 100 fields will be very inconvenient for users. Hard to imagine someone adding a new record and having to fill all 100 fields.
Post the list of Excel field heading here and someone can provide a better advice. It all depends on what sort of data is there.
D
 		DUKE author 11/22/2013

Hi Sergey,
Thanks for your answer, unfortunately I cannot post the table headings since the client is pretty secretive about this project until it is running.
The personnel section has a number of rather different categories like:
Personal Information - 18 Fields

Vat and Tax - 30 Fields

Contact Details - 45 Fields

Legal - 10 Fields

Conflict - 20 Fields

Safety - 12 Fields
The other issue is that I will have to make use of a bunch of Master/Detail Tables as well. For instance, under "Legal", "Vat" and "Safety" we need the facility to add a number of entries in a detail table.
My main problem is the onscreen layout of the fields for the user to fill in. If I have everything in one table, the onscreen layout becomes a problem since I cannot nest tabs and sections on the phprunner "Field Orders and Totals" page.... Some of the users will not be very bright, so everything has to be neatly laid out.
I am going to try this: <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=72981&image=1&table=forumreplies' class='bbc_emoticon' alt=':huh:' />

  1. Create one table with all the records. (Making sure that all fields are unique.)
  2. Create different views for the categories - "Personal Information", "VAT and Tax", "Contact Details" etc....
    The personnel then use the different views to edit different parts of their record. (Each Personnel Member will have only one record, which makes this easier).
    I can then use the tabs and sections to split up the views a bit.
    At the start of the project, a lot of personnel records will be imported. From there the personnel will log in and keep their information updated.
    The importing process will also be a lot easier if I work with only one table.... With multiple tables you will have to use multiple imports.
    Also I am a bit wary of the data integrity issues you mentioned.