0

Suppose you have these fields to keep track of in records: (first_name, last_name, state_id, stateid_status, day, month, year, id_status, id, criminal_status, charge, plea, sentence, create_time, update_time, hashID). If you were designing the database/table(s), would you create a single table, or would you divide the fields over multiple tables?

Why?

P.S. stateid_status, criminal_status, and id_status refer to the existence of those elements (e.g., criminal history either does or doesn't exist, the entity either does or doesn't have a state ID, etc.)

  • 2
    It really depends on what you intend on doing with that table, and the nature of the data that it will hold. For example, a given status may change over time for a person, and you may want to know which status they had at any given time, not just the current one. Same thing with charges. But in the end it will depend on the nature of the application. – Ziggy Crueltyfree Zeitgeister Apr 19 '16 at 5:40
2

It's better to extract some rarely used fields to another table. Especially if those fields are big (blob, long varchar etc). This will case smaller row size and better optimized buffer usage. Because small data (i.e 'id', 'name', 'status') will be stored in buffer and will have fast access to it. And queries with big fields (i.e. 'description TEXT') will be slower, because of need to read second table which can be unbuffered yet.

So, I think in your case there is no reason to split fields into different tables.

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.