0

I am learning MVC and SQL and I am trying to make an application that takes daily user data which is put in a day table. The day is linked to a week table and the weeks are linked to a year tables. I have entities for all 3. I am having problems with linking and orgainising them though. When I try link them it always uses their Primary Keys and so I am getting duplicate errors.

For example, when I have it that the user inputs the primary key value, and try to make Week 1 in, say 2015, but there is already a Week 1 in 2014, I get an error because they both have the same ID.

But when I have the computer automatically do the primary keys (so it goes 1, 2, 3, ...), I am only able get the days linked to the primary key. E.g. I have WeekID (the Primary Key) and WeekNo in the model. I have week 1 and 2 made and filled out, then go to make week 3 but accidentally make WeekNo 4, it's WeekID would still be 3 as it is the third week made. I delete it and make a new one and put in 3 as the WeekNo, then the WeekID is 4. This is a problem because then when I make a day, and click the dropdown menu for picking the week it goes in, I get the options "1, 2, 4" rather than "1, 2, 3" because it is using the primary keys. Even if the user never makes a mistake and has to delete it, this is still a problem when the next year comes around and the week numbers repeat.

My question is, does any one know how I could go about making it so that there are no duplicate problems and that they can be linked by something other than the primary key? Basically, when the user makes a week, they can select what year it goes into out of available years from the create page of Week. And the same when making a day, that they can select which week it goes in by number. Has any one encountered this kind of problem and gotten past it?

EstevaoLuis
  • 2,422
  • 7
  • 33
  • 40
Schyup
  • 1

1 Answers1

0

You are inventing calendar actually. I would rather have one column with type of TIMESTAMP. And on the server side/ or via SQL queries calculate day/week/year number (you didnt mention which RBDM you use). That will prevent you from having relation issues and also will help you in manipulating TIMEZONES in the future.

Here is the post for JAVA sample where week number is calculated

Otherwise you will need to use composite primary keys for:

  • day - (DAY_ID, WEEK_ID, YEAR_ID)
  • week - (WEEK_ID, YEAR_ID)
  • year - (YEAR_ID)
Community
  • 1
  • 1
fed.pavlo
  • 251
  • 1
  • 9
  • Would you still be able put it into tables with that? Sorry, I am not familiar with RBDM. I am only making this in visual studio yet and haven't really gotten onto any sql yet. – Schyup Jul 28 '14 at 10:21
  • Both cases will allow you to solve your problem. But first solution is more accurate. Walk through w3schools sql tutorial and in 1 hour you will be familiar with relational SQL. – fed.pavlo Jul 28 '14 at 10:30
  • I think the composite keys suit me better. How do I go about them if you don't mind my asking? – Schyup Jul 28 '14 at 10:32
  • Composite PK example: CREATE TABLE WEEK ( WEEK_ID int, YEAR_ID int, PRIMARY KEY (WEEK_ID, YEAR_ID) ); – fed.pavlo Jul 28 '14 at 10:34
  • How do you do that in c# for the MVC? – Schyup Jul 28 '14 at 10:37
  • I'm not c# dev, sorry( . Here is the stack post on it http://stackoverflow.com/questions/5962557/multiple-primary-key-with-asp-net-mvc-3 – fed.pavlo Jul 28 '14 at 10:39