Simple database /data storage and analysis solution?

HangTime · 19 Aug 2013 at 23:30

-45 seems short for comments assuming it is freetext. For example, the very short sentence I just wrote, not including this one, is over 45 characters.
-If staffId isn't the natural key (i.e. employee number) it may be worth adding a natural key to the Staff table
-Have you considered having a "Stable" table and then linking to it from horse with StableId as the foreign key as I would have thought there is a 0...n relationship between stables and horses, and you may find yourself wanting to hold more than just the stable name in future(?)
-Should passportNo definitely be LONG datatype i.e. will it only ever hold numbers?
-Assuming sex means gender I'm not convinced it should be BOOLEAN as it isn't obvious what this means i.e. purely looking at the schema how would anyone know if 1 means male or female? This may be parsed in the front end but I would recommend making your database design as unambiguous as possible.

PMKeates · 19 Aug 2013 at 23:35

Can a WorkProgramme only have one staff member and one horse?

Can a FieldVisit only have one staff member, one horse and one loaner?

Energize · 21 Aug 2013 at 19:37

HangTime said:
-45 seems short for comments assuming it is freetext. For example, the very short sentence I just wrote, not including this one, is over 45 characters.

I agree, isn't 255 the max for varchar? It doesn't seem like even that would necessairly be enough. Is there a better datatype/object I can use?

And on that subject what about if we wanted to have photos brought up in the views of horses for example, would photos be stored in the database itself as an object/datatype or reference from elsewhere?

-If staffId isn't the natural key (i.e. employee number) it may be worth adding a natural key to the Staff table

Why is this?

-Have you considered having a "Stable" table and then linking to it from horse with StableId as the foreign key as I would have thought there is a 0...n relationship between stables and horses, and you may find yourself wanting to hold more than just the stable name in future(?)

To clarify horses have two names, their registered or competition name like you see on the grand national etc, and a stable name that's used on a normal day to day basis, ie their real name.

-Should passportNo definitely be LONG datatype i.e. will it only ever hold numbers?

Suppose it's a possibility that the datatype could change if passport regulations change but currently passports are all long numbers.

-Assuming sex means gender I'm not convinced it should be BOOLEAN as it isn't obvious what this means i.e. purely looking at the schema how would anyone know if 1 means male or female? This may be parsed in the front end but I would recommend making your database design as unambiguous as possible.

Yeah I've changed that now, I also realised there are more than two genders anyway, ie mare/gelding/stallion, and any potential freaks of nature.

PMKeates said:
Can a WorkProgramme only have one staff member and one horse?

Yes as it's currently (badly in my opinion) implemented, the table name is probably ambiguous in that regard too admittedly. Every day that a horse is worked the staff member who worked it enters what activity they did with it and the date.

Can a FieldVisit only have one staff member, one horse and one loaner?

Based on the current information I have yes, as far as I'm aware a staff member visits a horse which is loaned to one person. And that visit is entered as a record in the database. That's certainly always the current situation, I suppose that there's a possibility in the future that a horse may have multiple loaners (which would cause problems with other tables too I guess), though I don't see why a seperate visit record could not be added for each horse even in that scenario, to make there only one m-m relationship there, though I suppose that's probably not very elegant in the long run.

Thanks for your advice and questions guys, I've come away with quite a bit to think about.

Ethermaster · 21 Aug 2013 at 23:50

the problem I have personally with the way you have designed the database is the lack of ID that can be used as an "INNER JOIN" or "FULL JOIN", as each horse is unique I would use an unique horse ref number or something to link the tables together.

Energize · 22 Aug 2013 at 00:43

Please could you elaborate? Is horseId not the same as a unique reference number?

Ethermaster · 22 Aug 2013 at 08:20

Energize said:
Please could you elaborate? Is horseId not the same as a unique reference number?

No problem

I would personally create the database in a "hierarchy" making tbl.Horse the prime table which means that the key ID needs to appear in each major table so that reporting across tables can be done easily via something like Business objects, using "INNER JOIN" or "FULL JOIN" across the tables.... also it would be purdent to create an table to record any changes made to the database and who made it for auditing.

Energize · 22 Aug 2013 at 13:58

Can anyone recommend any client software for the end users? Want something that allows the average user who doesn't know anything about databases and sql, entities relationships etc to access and edit data without it looking like some daunting project lol.

Energize · 22 Aug 2013 at 18:34

Or am I just better off creating my own front end? Maybe mod one of those php style ones?

Energize · 22 Aug 2013 at 18:37

Ethermaster said:
also it would be purdent to create an table to record any changes made to the database and who made it for auditing.

Hmm didn't realise you typically have the auditing data in a table itself! But we have for example, made sure every person has their own unique account so such things can be logged.

HangTime · 25 Aug 2013 at 23:13

Energize said:
I agree, isn't 255 the max for varchar? It doesn't seem like even that would necessairly be enough. Is there a better datatype/object I can use?

Depends on the DBMS, I would check what the max is for your platform and version. SQL Server definitely has a much higher limit. Googling suggests MySQL 5.0.3 and above has a much higher limit than 255.
If you want very long character strings have a look at CLOB but I would question whether you really need it for a simple comments field.

And on that subject what about if we wanted to have photos brought up in the views of horses for example, would photos be stored in the database itself as an object/datatype or reference from elsewhere?

I'm not qualified to make a recommendation but you could do either. Typically binary data (images, documents etc) might get stored as in BLOB fields if you wanted to keep them in the database. If you go down that route I'd definitely do some research into maintenance plans and such like (reclaiming space for deleted photos etc).

Energize · 27 Aug 2013 at 04:13

Sounds good, thanks.

I've revised it a bit as you can see (ctrl + f5), but still a couple of questions.

Currently they have vet records, physio records, farrier records, worming records. I don't know how best to represent this, currently I have one treatment record where the type represents the type of record, but that doesn't seem very elegant to me?

Also the same situation exists with contacts, they have vets, farriers etc as contact types, surely there is a better way to represent this than a type field?

Mercutio · 27 Aug 2013 at 14:38

just as a random one to throw in you could look at oracle apex. As I understand it, it's a stripped down oracle DB with simpler development tools.

Energize · 3 Sep 2013 at 01:31

With regards to creating a front end for the database, what are peoples opinions on web vs desktop application? Advantages I can see to using something like php are that it will work on any device without having to install any software.