4 Replies - 416 Views - Last Post: 23 November 2018 - 12:25 PM Rate Topic: -----

#1 SchizoCoder   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 16-July 18

Designing database entities with versioning

Posted 22 November 2018 - 07:57 AM

I'm developing a database in SQL Server for a web application in ASP.NET MVC Core and Entity Framework. I'd like to get your input on my database design, since I don't have very much database design experience. This application has a spreadsheet-like interface that shows the software licenses (Products) and the company Teams, with how many licenses of each software product each team has.

There are currently three main entities, Product, Team, and Worksheet. Each of those entities has a related details table that contains the data, e.g. ProductDetails, which has the data for Product for each worksheet.

Actually, WorksheetDetails is more of a mapping table, so I might want to rename that. Any suggestions for a better name would be appreciated, especially if there is a naming convention for such tables.

The Product table itself just has ID and the name of the product. ProductDetails has several columns of data and a foreign key to Product. Same for Team and TeamDetails. Worksheet is just the ID, Name and metadata for each worksheet. WorksheetDetails has foreign keys to Product, Team, and Worksheet, along with a column for values that each Product/Team combination has (NumLicensesPerProductPerTeam).

Here is the database diagram for the database:

Attached Image

One thing I'm uncertain about, at least when looking at other database designs in tutorials all over the web, is that my design separates the entity's data from its ID and Name, whereas almost no tutorials I've seen do that, so I'm wondering if I'm doing something wrong. I did this because the data can change for each worksheet, but the ID and Product (or Team) name won't likely change very much, only, for example, when a team gets split, which is going to happen pretty soon with one of the teams.

I see that it doesn't make sense for each row in WorksheetDetails to have ProductDetailsId AND TeamDetailsId, since there can be a variable number of products and teams in each worksheet. What do you think about changing WorksheetDetails so that instead of having the two foreign keys ProductDetailsId and TeamDetailsId, there should be a single foreign key and a discriminator column that has either "Team" or "Product". Either that or just keep the two foreign key columns but allow one of them to be null for each row (is it even possible to enforce one or the other being required at the database level?). Also, I can move the NumLicensesPerProductPerTeam attribute to a separate table (maybe called ProductTeam) where each row actually should have ProductDetailsId and TeamDetailsId, along with NumLicensesPerProductPerTeam and any other attributes that are related to both Product and Team.

By the way I probably gave this post the wrong title, but anyway...

Is This A Good Question/Topic? 0
  • +

Replies To: Designing database entities with versioning

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14862
  • View blog
  • Posts: 59,306
  • Joined: 12-June 08

Re: Designing database entities with versioning

Posted 22 November 2018 - 11:24 AM

Of all of that - which parts are you looking to keep 'versioned' or have a history on?
Was This Post Helpful? 0
  • +
  • -

#3 SchizoCoder   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 16-July 18

Re: Designing database entities with versioning

Posted 23 November 2018 - 04:05 AM

Initially I was going to ask about versioning, hence the title of my original post (and why I said at the end of my post that I named the topic wrongly, since I didn't mention versioning in the post). I kind of changed the topic as I was writing it. Sorry about that.

My reason for extracting the Product and Team data into separate details tables was to address that the data from one worksheet to the next could vary over time.

So to answer your question, the ProductDetails and TeamDetails tables keep the version of data for each worksheet, for each team/product. Now that I think of it, ProductDetails and TeamDetails can't really exist independently of a worksheet (or at least, they probably shouldn't) so maybe the data in ProductDetails and TeamDetails should be moved into columns in WorksheetDetails.

Any suggestions?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14862
  • View blog
  • Posts: 59,306
  • Joined: 12-June 08

Re: Designing database entities with versioning

Posted 23 November 2018 - 06:42 AM

Ah, ok. I see what you were saying now about the title being wrong.

The things I would change would be to collapse these tables a bit.

Fold product details into product name.
Fold worksheet details into worksheets.
Team details into team.

Though I am not sure if 'worksheet' is really needed to be anything complex other that just a product_id to team_id two column table.
Was This Post Helpful? 0
  • +
  • -

#5 SchizoCoder   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 16-July 18

Re: Designing database entities with versioning

Posted 23 November 2018 - 12:25 PM

Okay, but if I fold the details back into Product and Team, then I will have the same ProductName and TeamName repeated each time the details need to be different for each worksheet. That's why I separated the details from the name, because I want the identity of each product and team to be maintained even though the data changes for each worksheet. Does that make sense?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1