This should be simple. But how do I design this?

I have 2 kinds of business units; division and department. A business units has to be one or the other, but cannot be both. So this is easy enough. Have a BusinessUnit table and a Busine开发者_开发知识库ssUnitType lookup table containing division and department. However only divisions can contain teams. For each division there are one to many teams. Departments do not have teams. So what should I be doing here? Maybe I should have a flag on the BusinessUnitType table called hasTeam? Is that the best way to organise this data? I am not sure if this particular design has a name.

---

Your case looks like an instance of the gen-spec design pattern. Gen-spec is short for “generalization specialization” (see definition ). The gen-spec pattern is familiar to programmers who understand inheritance. But implementing the gen-spec pattern in a relational schema can be a little tricky, and database design tutorials often skip over this topic.

This topic has come up before. (See sample discussion).

Fortunately, there are some good articles on the web that explain just this subject (see sample article). And a Google search (see sample search) will yield lots more articles.

---

It's a bit difficult to answer without a broader context (for example- what technologies you're using, whether this is a new project, whether you have any other constraints on implementation etc.).
but generally speaking I would say:

1. If this is a new project, and you are not technology-constrainted, I'd recommend using an OR/M (I personally use nHibernate). You can easily configure it to fit your needs without worrying about the underlying DB.

2. otherwise- it seems that your original thought is a good idea. Depending on the DB you're using, you can probably create a constraint to enforce that logic, if you like (I personally would not recommend that, since it brings your business logic into your DB, where it doesn't belong).
   Hope this helps.