1

I wanted to know how do people generically think of, in a data base table:

  • Columns that people are expected to use mostly for grouping expectable of groupability.

  • Columns that people are expected to use mostly for actual business data that not.

For instance, let us consider a quick example of a school. I would say the nth grade might be a column used mostly for grouping, while the mark, qualification, assessment whatever, I would say that is a column used mostly for actual business data. Which would be a generic name for those kind of data base columns?

Annex

For instance, let us consider a quick example of a school. I would say the nth grade might be a column used mostly for grouping, together with the mark, qualification, assessment whatever, but I would not say their passwords' MD5 hashes (I guess if the context was a consumer application where those students would log in) will be ever used for grouping. Which would be a generic name for those kind of data base columns? Is there a concept for column cardinality or grouping-ness?

Improve this question
3
  • 1
    Why makes you assume "mark", "qualification", "assessment" will not be used for grouping? – Doc Brown Nov 23 '16 at 13:49
  • Good point, this school example is not good for what I am wanting to ask. – 94239 Nov 23 '16 at 14:40
  • Please edit to the best presentation possible, don't keep around old stuff, but also don't edit in a way that invalidates reasonable posted answers, instead post a new question. – philipxy Sep 23 '20 at 19:16
1
+200

I know this is old, but no-one has provided an answer to his base question as it was asked.

The most basic (non business-related) answer to your question of what columns are "expectable of groupability" is as follows:

  • Columns on which one would/could group are columns having repeating values over the rows.
  • Columns on which one would/could not perform grouping are columns having unique values across the rows e.g. no duplicate values across rows (in which case, no "grouping" would occur, even if you use it in the query).

This is the most generically-applying answer (which is exactly what you asked for in your question) and it applies the same regardless of how the data is modeled and other factors mentioned in the other answers, etc.

You gave an example of a column on which you didn't expect there to be an expectation of grouping:

  • md5_hash - lets say your table is UPLOADS and a column is MD5_HASH which holds the value of a hash of the file (same as your password scenario). Lets say the admin wants to find cases of multiple uploads of the same file. The analyst would typically group by MD5_HASH having count(*) > 1. Or going with your password hash scenario - one might want to find instances of users using common passwords, or perhaps run ad-hoc procedures to suggest duplicate accounts (in which identical passwords would be one input). This would generally be done by the same group by ... having ... clauses.

I've been in the DWBI space for over 15 years, and in my experience, there are always "power users" of the data who will, over time, be writing all manner of queries to look at the data every which way you can never have imagined... so you can usually be sure that, in a DW setting, any column that contains repeating values could be grouped on by an ad-hoc SQL query at some point.

Improve this answer
2

They are all "actual business data", save surrogate PK (and FKs that point to them) that can be used for grouping and searching but are meaningless business-wise. All other columns can also be used for grouping and searching and they still are "actual business data".

Not everything has an specific name or can be pigeon-holed into any arbitrary taxonomy.

Improve this answer
6
  • I would call foreign keys "actual business data" also, as they are the expression of the relationship, even if the pointee is a surrogate key - it represents the whole row – Caleth Nov 23 '16 at 12:11
  • @Caleth Surrogates have no business meaning, FKs pointing to surrogates are also meaninless sinces they are a copy of the surrogates themselves. Also I'm not sure what you mean by "it represent the whole row". – Tulains Córdova Nov 23 '16 at 12:22
  • because the purpose of a FK is enforcing "joinability" between tables. That there is a row in table Parent because table Child has a FK to Parent's PK is not meaningless – Caleth Nov 23 '16 at 12:24
  • @Caleth Using that rationale, then the same could be said of surrogate PKs since they are enforcing "unicity", which is a business requirement for that table. But it is universally understood that having business meaning is something else, and almost any source says that surrogates have no business meaning and that is one of the "advantages" they have over natural keys. – Tulains Córdova Nov 23 '16 at 12:30
  • a surrogate PK doesn't enforce "unicity" in the presence of an other unique constraint. I would claim that the implicit unique constraint is what provides the uniqueness in a surrogate PK, and the underlying datum is meaningless – Caleth Nov 23 '16 at 12:36
0

If you want to know how to properly think about database tables, study a fact-oriented modeling discipline like object-role modeling. Tables represent predicates (fact types), columns represent roles in the predicates and rows represent propositions (facts).

Improve this answer

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.