Any time information is one-to-one (each user has one name and password), then it’s probably better to have it one table, since it reduces the number of joins the database will need to do to retrieve results. I think some databases have a limit on the number of columns per table, but I wouldn’t worry … Read more
If performance is the primary concern, I would go with #6… a table per UDF (really, this is a variant of #2). This answer is specifically tailored to this situation and the description of the data distribution and access patterns described. Pros: Because you indicate that some UDFs have values for a small portion of … Read more
Cascade will work when you delete something on table Courses. Any record on table BookCourses that has reference to table Courses will be deleted automatically. But when you try to delete on table BookCourses only the table itself is affected and not on the Courses follow-up question: why do you have CourseID on table Category? … Read more
I have done this two ways in the past – a single row table and a key/value pair table – and there are positives and negatives to each approach. Single Row positive: the values are stored in the correct type positive: it is easier to deal with in code (due to the above) positive: default … Read more
Not all data is relational. For those situations, NoSQL can be helpful. With that said, NoSQL stands for “Not Only SQL”. It’s not intended to knock SQL or supplant it. SQL has several very big advantages: Strong mathematical basis. Declarative syntax. A well-known language in Structured Query Language (SQL). Those haven’t gone away. It’s a … Read more
No, there is no “better” way to store a sequence of items in a single column. Relational databases are designed specifically to store one value per row/column combination. In order to store more than one value, you must serialize your list into a single value for storage, then deserialize it upon retrieval. There is no … Read more
I would use VARCHAR for variable length data, but not with fixed length data. Because a SHA-1 value is always 160 bit long, the VARCHAR would just waste an additional byte for the length of the fixed-length field. And I also wouldn’t store the value the SHA1 is returning. Because it uses just 4 bit … Read more
A “correlated subquery” (i.e., one in which the where condition depends on values obtained from the rows of the containing query) will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query) will execute once at the beginning. The SQL engine makes this distinction automatically. … Read more
There are several ways to model inheritance in a database. Which you choose depends on your needs. Here are a few options: Table-Per-Type (TPT) Each class has its own table. The base class has all the base class elements in it, and each class which derives from it has its own table, with a primary … Read more
One method that is used by a few wiki platforms is to separate the identifying data and the content you’re auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an … Read more