- Defining foreign keys would make the relationship between tables clearer. (Unfortunately, MyISAM tables ignore foreign key constraints MyISAM tables ignore foreign key constraints. However, even if you are using MyISAM, I'd write the foreign key constraints anyway out of habit, to make the intended relationships clear, and to prepare for the possibility of migrating to a better database in the future.)
- The
Questions.qtypecolumn contains some kind of integer code whose meaning will be apparent only from reading the application code. Either use anENUM, aVARCHAR, or at least write a comment. - What is
Answers? Does it contain the pre-determined multiple-choice answers (in which case I would expect some indication of which is the correct answer)? Or does it contain the responses given by the quiz taker (in which case I would expect another column for the user's identity or the quiz session).
I'm puzzled by the use of both
INTEGER(4)andINTEGER(8). Why not use just one type consistently? (Do you really expect to have more than 2 billion of anything?)It's unconventional that your primary keys are not
AUTO_INCREMENT.VARCHARcan support more than 255 bytes since MySQL 5.0.3VARCHARcan support more than 255 bytes since MySQL 5.0.3 — the actual limit is 64 kiB per row. However, if you have any reason to believe that theVARCHARlimit might be exceeded, just useTEXT. For a quiz application, I doubt that you'll notice any performance difference. (TEXTcolumns are searchable. ABLOBcontains binary data, and you probably wouldn't want to search binary data anyway.) MySQL has a nasty habit of truncating text that exceeds the declared size with little more than a log message:If strict SQL mode is not enabled and you assign a value to a
CHARorVARCHARcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, "Server SQL Modes".Consider storing the entire image contents, not just the filename, in the database. Splitting your data between the database and the filesystem leads to complications:
- Backup procedures have to be coordinated to ensure that your filesystem and database backups are consistent with each other.
- If you ever have to deploy the application on a load-balanced server farm, you'll either need to find a mechanism to copy the same data files to all servers, or serve them from a network filesystem.
Admittedly, serving the images from the database through your application code will be slower and more complicated than just serving files from the filesystem. A hybrid system, in which the database contains the authoritative image data, and the application can dump them to the local filesystem for webserving, might be ideal.
You named one column
CompoundQuestionText.imgname, and another oneAnswers.img. Consistent naming would be nice.
- Defining foreign keys would make the relationship between tables clearer. (Unfortunately, MyISAM tables ignore foreign key constraints. However, even if you are using MyISAM, I'd write the foreign key constraints anyway out of habit, to make the intended relationships clear, and to prepare for the possibility of migrating to a better database in the future.)
- The
Questions.qtypecolumn contains some kind of integer code whose meaning will be apparent only from reading the application code. Either use anENUM, aVARCHAR, or at least write a comment. - What is
Answers? Does it contain the pre-determined multiple-choice answers (in which case I would expect some indication of which is the correct answer)? Or does it contain the responses given by the quiz taker (in which case I would expect another column for the user's identity or the quiz session).
I'm puzzled by the use of both
INTEGER(4)andINTEGER(8). Why not use just one type consistently? (Do you really expect to have more than 2 billion of anything?)It's unconventional that your primary keys are not
AUTO_INCREMENT.VARCHARcan support more than 255 bytes since MySQL 5.0.3 — the actual limit is 64 kiB per row. However, if you have any reason to believe that theVARCHARlimit might be exceeded, just useTEXT. For a quiz application, I doubt that you'll notice any performance difference. (TEXTcolumns are searchable. ABLOBcontains binary data, and you probably wouldn't want to search binary data anyway.) MySQL has a nasty habit of truncating text that exceeds the declared size with little more than a log message:If strict SQL mode is not enabled and you assign a value to a
CHARorVARCHARcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, "Server SQL Modes".Consider storing the entire image contents, not just the filename, in the database. Splitting your data between the database and the filesystem leads to complications:
- Backup procedures have to be coordinated to ensure that your filesystem and database backups are consistent with each other.
- If you ever have to deploy the application on a load-balanced server farm, you'll either need to find a mechanism to copy the same data files to all servers, or serve them from a network filesystem.
Admittedly, serving the images from the database through your application code will be slower and more complicated than just serving files from the filesystem. A hybrid system, in which the database contains the authoritative image data, and the application can dump them to the local filesystem for webserving, might be ideal.
You named one column
CompoundQuestionText.imgname, and another oneAnswers.img. Consistent naming would be nice.
- Defining foreign keys would make the relationship between tables clearer. (Unfortunately, MyISAM tables ignore foreign key constraints. However, even if you are using MyISAM, I'd write the foreign key constraints anyway out of habit, to make the intended relationships clear, and to prepare for the possibility of migrating to a better database in the future.)
- The
Questions.qtypecolumn contains some kind of integer code whose meaning will be apparent only from reading the application code. Either use anENUM, aVARCHAR, or at least write a comment. - What is
Answers? Does it contain the pre-determined multiple-choice answers (in which case I would expect some indication of which is the correct answer)? Or does it contain the responses given by the quiz taker (in which case I would expect another column for the user's identity or the quiz session).
I'm puzzled by the use of both
INTEGER(4)andINTEGER(8). Why not use just one type consistently? (Do you really expect to have more than 2 billion of anything?)It's unconventional that your primary keys are not
AUTO_INCREMENT.VARCHARcan support more than 255 bytes since MySQL 5.0.3 — the actual limit is 64 kiB per row. However, if you have any reason to believe that theVARCHARlimit might be exceeded, just useTEXT. For a quiz application, I doubt that you'll notice any performance difference. (TEXTcolumns are searchable. ABLOBcontains binary data, and you probably wouldn't want to search binary data anyway.) MySQL has a nasty habit of truncating text that exceeds the declared size with little more than a log message:If strict SQL mode is not enabled and you assign a value to a
CHARorVARCHARcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, "Server SQL Modes".Consider storing the entire image contents, not just the filename, in the database. Splitting your data between the database and the filesystem leads to complications:
- Backup procedures have to be coordinated to ensure that your filesystem and database backups are consistent with each other.
- If you ever have to deploy the application on a load-balanced server farm, you'll either need to find a mechanism to copy the same data files to all servers, or serve them from a network filesystem.
Admittedly, serving the images from the database through your application code will be slower and more complicated than just serving files from the filesystem. A hybrid system, in which the database contains the authoritative image data, and the application can dump them to the local filesystem for webserving, might be ideal.
You named one column
CompoundQuestionText.imgname, and another oneAnswers.img. Consistent naming would be nice.
I must admit, I have trouble understanding your schema. Part of the problem is that I don't quite grasp what you mean by a CompoundQuestion. Mostly, though, I think that the schema isn't self-documenting. Since I don't really understand your schema or your additional concerns, I'll just make some remarks on what I do see.
Self-documenting
- Defining foreign keys would make the relationship between tables clearer. (Unfortunately, MyISAM tables ignore foreign key constraints. However, even if you are using MyISAM, I'd write the foreign key constraints anyway out of habit, to make the intended relationships clear, and to prepare for the possibility of migrating to a better database in the future.)
- The
Questions.qtypecolumn contains some kind of integer code whose meaning will be apparent only from reading the application code. Either use anENUM, aVARCHAR, or at least write a comment. - What is
Answers? Does it contain the pre-determined multiple-choice answers (in which case I would expect some indication of which is the correct answer)? Or does it contain the responses given by the quiz taker (in which case I would expect another column for the user's identity or the quiz session).
Column definitions
I'm puzzled by the use of both
INTEGER(4)andINTEGER(8). Why not use just one type consistently? (Do you really expect to have more than 2 billion of anything?)It's unconventional that your primary keys are not
AUTO_INCREMENT.VARCHARcan support more than 255 bytes since MySQL 5.0.3 — the actual limit is 64 kiB per row. However, if you have any reason to believe that theVARCHARlimit might be exceeded, just useTEXT. For a quiz application, I doubt that you'll notice any performance difference. (TEXTcolumns are searchable. ABLOBcontains binary data, and you probably wouldn't want to search binary data anyway.) MySQL has a nasty habit of truncating text that exceeds the declared size with little more than a log message:If strict SQL mode is not enabled and you assign a value to a
CHARorVARCHARcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, "Server SQL Modes".Consider storing the entire image contents, not just the filename, in the database. Splitting your data between the database and the filesystem leads to complications:
- Backup procedures have to be coordinated to ensure that your filesystem and database backups are consistent with each other.
- If you ever have to deploy the application on a load-balanced server farm, you'll either need to find a mechanism to copy the same data files to all servers, or serve them from a network filesystem.
Admittedly, serving the images from the database through your application code will be slower and more complicated than just serving files from the filesystem. A hybrid system, in which the database contains the authoritative image data, and the application can dump them to the local filesystem for webserving, might be ideal.
You named one column
CompoundQuestionText.imgname, and another oneAnswers.img. Consistent naming would be nice.