Things to change in the database

[eugene-doe]

The DECIMAL data type from Create_Tables translates to DECIMAL (18) in the actual table definitions, which equals DECIMAL(18,0) and is not right: it means 0 digits after the decimal point.

For latitude and longitude it should be DECIMAL(10,7) (or more, but at least 7 digits after the decimal point and at least 3 before = 10 in total). Weight probably doesn't need that many digits.

Mirya, I've "assigned" you, since I think you wanted to change something in the database anyway. :)

[miryanezvitskaya]

Ok, Sounds good. Yes, I am planning to make lots of changes in the database. So if someone comes across something else, let's keep this issue open for that (Also "fist name" has to be changed).

[eugene-doe]

One more thing: I think we should change VARCHAR to NVARCHAR in all instances. Reason: http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar

[eugene-doe]

It appears that for some things you don't really need to rebuild the model after you make changes to the DB. For instance, I tried changing the DECIMAL types to DECIMAL(18,9) in my local database, and everything seems to work -- I am now able to use real coordinates, not just integer values. Of course, this way the database is inconsistent with the model, but it will do for now.

[triina]

In table Lure please change the names of the lures to be different from each other. Also make lure types that would not have any lures associated with them.

It would be nice to have those kind of ¨free¨ data elsewhere as well. So Fishermen who is not in any sessions, lures that have no catch, fishspecies that are not in any catch, sessions that have no catch, methods/locationmarkings/ that have no sessions, waters that are not yet part of any locationmarking. Wow. That sentence is not really readable, so let me know if you fail to see a point.

[Nonse]

if you haven't done db yet.. I forgot to mention changes in Fishing Method table. it needs real descriptions (probably ones from the course pages)

[eugene-doe]

Oops... Looks like we need a DB update again. :) Something seemed strange today on Catch and Session pages, and I've just realized what it was: the relationship between these tables is reversed. Look at this diagram and compare it to ours: http://myy.haaga-helia.fi/~bit2007/itp1tf013/Assignments_2014/Fisherman_ER_with_attributes.png

It's the Catch table that should reference FishingSession via a foreign key, not vice versa. Now we can create a catch that is not related to any session, and each session can have only one catch.

And if we are going to make changes, we can also remove NOT NULLs from descriptions. I think they don't have to be mandatory, in real apps they never are.

[eugene-doe]

...and if we follow Markku's model, Catch should also have fisherman id as a foreign key. If we are going to display a list of all catches for a particular fisherman, this can make life easier.

[eugene-doe]

...and can we also have ON DELETE CASCADE everywhere instead of the "Cannot delete this item" messages? :) The closer we are to the UI design stage, the more usability issues come to mind...

[eugene-doe]

Did we have something about this IDENTITY keyword on the course? I've just read about it and it seems familiar. I'm not suggesting to implement it now, after we've done all the extra work to auto-increment ids, but here's how we could have done it without reinventing the wheel: http://www.w3schools.com/sql/sql_autoincrement.asp

[triina]

So one idea was to make descriptions voluntary. Sounds good to me. But bear in mind that I think in water the description perhaps should be kept because there are no unique names. Another idea was to make it so that fisherman cannot add methods. Because it would not look nice without icons.

[Nonse]

We should reconsider the concept of the Fishing Method table. Now it allows user to add new fishing methods and it will make more sense when only five provided methods are available.

// I get frustrated when I think of asking Markku if there are more fishing methods irl than they gave us to work with..

[eugene-doe]

I don't think we really have to make the table read-only in the database. It's the application that should not provide the functions to add/edit/delete fishing methods, and I think (for this project) it is sufficient to simply remove the links to the corresponding pages from the master view.

[Nonse]

Agree. Seems like simple solutions never come to my mind :)

[eugene-doe]

Some comments to the new database:

1. Foreign keys for Session and Catch have been reversed. The model is up-to-date with the database (all annotations in place), but the controllers and views for these two tables need some revision. I think we can just delete these controllers and views, recreate them and then modify as needed, since not too many modifications have been done to them so far.
2. Descriptions are no longer mandatory in any tables.
3. I've only added ON DELETE CASCADE to Catch for now, since this is where it is most likely to be needed (and if something goes wrong, a single change is easier to undo).

I'm closing the issue, since I hope this is our final database for the project.