In general, how you organize your data very much depends on the data itself. It is so contextual that you can't really give any generalized advice that applies universally.
To keep things simple, let's invent a data set:
- A - Instant coffee
- B - Black bean coffee
- B1 - Freshly ground black bean coffee
- B2 - Roasted black bean coffee
- C - Red bean coffee
- C1 - Freshly ground red bean coffee
- C11 - Brand ABC
- C12 - Brand DEF
Since you want to interchangeably reference either a coffee group or subtype, it makes sense to model this data as a single recursive set. That is to say, one table contains everything, and some of its entries refer to other entries of the same table.
Focusing solely on the PK and parent FK, the table would contain something like:
PK |
Parent FK |
A |
NULL |
B |
NULL |
B1 |
B |
B2 |
B |
C |
NULL |
C1 |
C |
C11 |
C1 |
C12 |
C1 |
Keep in mind that this is an oversimplification. In reality, you'd be much better off using a database-generated ID (i.e. int or guid) for PK/FK. I just used the string codes here to make it easier to understand which entry refers to which.
Also, I omitted other columns such as the name of the coffee type or any other data you might want to store here. Of course, you can add these as you see fit.
On a code level, you can establish the same entity, i.e. something along the lines of:
public class CoffeeType
{
public string Code { get; set; }
public string ParentCode { get; set; }
}
Again, using string
is an oversimplification here. int
or Guid
are much preferred database key types.
And now, every other operation in your domain can interchangeably refer to either a coffee supertype or subtype. For example:
public class CoffeeMenuItem
{
public CoffeeType Type { get; set; }
public double Price { get; set; }
}
var menu = new List<CoffeeMenuItem>()
{
new CoffeeMenuItem()
{
Type = _repo.GetByCode("C"), // Price for the entire C group
Price = 4.50
},
new CoffeeMenuItem()
{
Type = _repo.GetByCode("B1"), // Price for a specific subtype
Price = 2.50
},
new CoffeeMenuItem()
{
Type = _repo.GetByCode("B2"), // Price for a specific subtype
Price = 2.75
}
};