As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Helping a friend out: creating a database
Aldo
Hippo HoorayRegistered User regular
Hippo HoorayRegistered User regular
Hey,
I'm making this topic on request of a friend, she's working on a farm and is in need of a database to keep track of some things, I'm copying this off the forum she asked it on, apparently the people over there weren't nerdy enough to help her out.
The tl;dr version is probably enough to say something smart about it.
I'm making this topic on request of a friend, she's working on a farm and is in need of a database to keep track of some things, I'm copying this off the forum she asked it on, apparently the people over there weren't nerdy enough to help her out.
The tl;dr version is probably enough to say something smart about it.
Transgender L A N Z E R wrote:Short version: I want to create a database for keeping work information in that accessable via login for adding to and editing (publicly viewable though) and information entered using a very simple and easy to use form like thing. If possible, I want this to be both something I can have running on a webpage and something I can run off my flash drive (so we can update it from netless work), with a way to import/export updated information between the two. Most import, as the information is added (and new enteries made) for pages containing that information to automatically update/generate.
If there's anyone willing to help or what not, it would be more than appreciated, but for the most part I'm really just looking for advice and tips from anyone who knows even a bit more than me on what's plausable and what to start looking into and learning to make this happen
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Attempting to explain further, but probably just going to sound even more confusing: The only thing that's really popping to mind for me is something that basically has all the information stored on a spreadsheet (or two or three) and page templates setup to replace specific spots on the page with type spreadsheet information (probably something that could be done with CSS or whatnot, but I really have no fucking clue XD).
If this helps things make anymore sense at all, this will be a database of all the horses, other animals, and staff at the farm(s). Each section (horses/other/staff) with have different information sections and different page layouts. With each entry for an animal/person there will most likely be multiple pages involved (at least for the horses). For example, here's what I have sketched out for the horse template so far:
Main Page/Profile: Horse - - -
The blue sections with the black x's through them represent pictures while black represents text only areas.
Not to scale; just for reference.
A: Main profile picture of the horse.
B: Contains most of the Horse's basic information - Name, Age, Birthdate, Birthplace, Height, Weight, Current Location, etc... Height and Weight would be linked to the Height and Weight history section of the horse's medical page and if dead, age would be replaced with 'Deceased'.
C: Thumbnails of any other pictures, each linked to the fullsize version (opens in new window, doesn't replace profile picture) and this section scrolls right and left.Text Link to the Lineage and Offspring page.
E: Text Link to the Medical/Health History Page.
F: Link to the Training History Page.
G: Link to the Race/Comeptition Information Page.
H: Scrollable text box of the Horse's personality descriptions.
I: Scrollable text box of the Horse's Physical Traits.
J: A Scrollable text box for special notes.
Lineage/Offspring Page - - -
The Lineage portion will only go back to grandparents. It would be nice if by adding the Sire or Dam here automatically added this horse to the Sire/Dam's offspring (and vice versa), but not necessary.
A: Horse's Sire: Main picture with Sire's name underneat (linked to Sire's profile if available).
B: Horse's Dam: " "
C: Sire's Sire: " "
E: Dam's Sire: " "
F: Dam's Dam: " "
G: List of Offspring: I haven't decided whether to do this in text only with names linked to the Offsprings profile, or thumbnails of the Offspring's Main pic linked to it's profile in a side scrollable bar much like section C of the main profile. Probably going to go text since the picture way leaves no room for osspring that don't already have a profile or any pictures.
Medical/Health Page - - -
A: Medical History scrollable text box.
B: Family Medical History scrollable text box: This is one of those things that would be cool if it automatically took the information from the Sire, Dam, etc.. Medical history section and listed it here, but not necessary.
C: Status on basic health care aspects: This includes a bunch of different section much like section B of the Main Profile for when they were last shoed, trimmed, wormed, had a vet check, etc...
E: A scrollable text section for any additional info.
Training History Page - - -
A: Scrollable text box for current training.
B: Scrollable text box for past training.
C: Side scrollable bar with thumbnails of staff members main picture that have trained or are currently training this particular horse (so once again, something much like the section C of the main profile page).
E: Scrollable textbox for additional notes.
Competition/Race Information Page - - -
A: Winning/Awards: Scrollable text box listing winnings and awards by most recent with the Event Name, Location, Date, Jockey/Rider, Event and Win description.
B: Current Competition/Race schedule: Scrollable text box listed by soonest first with Event Name, Location, Description, Date, Horse Number, and Jockey/Rider.
All of these pages would be for just one horse. So for inputing and editing the information, I would want myself and my co-workers tobe able to login into the database, be able to click on something like 'add horse', 'add animal', 'add staff', or and edit button which lets them select from drop down menus categoried by type (or better yet/in addition to an edit button on the main profile page once logged in). The edit/add page would be the same (except of course the edit page would already have any pre-entered information) and be a very simple form like think where everything can jsut be entered in, pictures added (main picture selected from picture uploaded from here), and so forth. Very very very simple and easy to understand from looking at. One of the co-workers is over the top computer illiterate XD
So yes... help, advice, clarifying questions...?
Aldo on
0
This discussion has been closed.
Posts
A DATABASE is the medium for storing all information on the horse, etc..
A WEBSITE is the medium for displaying all information.
Some thoughts in no particular order:
Where is this hosted, and what do they offer? mySQL & PHP hosting can be had for a very small amount.. and is the easiest availability. You need more then HTML, and you don't need oracle or other high $$ db system
CSS is great, but Tables are made to, well, display things in tables. There is nothing wrong with laying out all of this as a large table, especially if the relevant items are dynamically written.
Make a plot of ALL data you want for the horse, etc. Then you can begin you break it out into various DB tables & remove reduntant information ... the breakup of this information has NOTHING to do with how it is displayed, but rather how it is stored.
Those are the basic things you need to do.. and unless you want to start cracking books now.. you are probably going to pay a pretty penny for someone to create this. Its not a very quick thing... but once the data is identified, you can make a generic interface fairly quickly.
Librarians harbor a terrible secret. Find it.
1) Is this a permanent or temporary solution?
2) Approximately how much data do you expect to have in the first year? (IE, how many horses)?
3) Approximately how much data do you expect to have in three years?
4) How many people total are going to access it? (Today? In three years?)
5) How many people might be using it simultaneously? (Today? In three years?)
6) Will all these people be in the same physical location? (IE, an office or farmhouse?)
7) Does the application have to be accessible via the web, or can it be accessible strictly on a LAN? (I know she said that it needs to be on "a webpage" but that doesn't mean it necessarily has to be accessible from the [noparse]WWW.)[/noparse]
8) What operating system are they using?
9) What is their budget? Do they have a budget, or are they looking for ballpark estimates?
See, the cheapest and easiest - though not necessarily the best - way to do this would be a database application like Microsoft Access. But Access is really only feasible for small databases (under 10,000 records) with few users (under 25 simultaneous users) on the same LAN and no web functionality.
If they need better performance and scalability, then moving to web-enabled technologies, like SQL/PHP would be better. But that doesn't necessarily mean the system has to be on the web - if all the users are going to be in the same physical location, then you can keep the database and application hosted on a local server. If it absolutely has to be accessed from multiple locations, then getting it on the web is definitely an option, as long as the client understands that this will involve a recurring hosting fee from now until forever.
the "no true scotch man" fallacy.
She already has a plot of the data, she just doesn't know how to organise it storage-wise, she is currently basing it off on visual aspects, because she just doesn't know how else to explain it.
1) Trying to hammer out something permanent.
2) 80-300 horses, 10-20 staff/trainers, 50-100 other.
3) 20-50 more horses, 5-10 more staff/trainers, 10-25 more other.
4) Today: 3-4 In three years: 3-10 (but that would only include people who are accessing it with the ability to add and edit. As far as people who would only be viewing it, that would be more around 10-20 today, and not real sure about three years from now. A lot of that would really depend on whether or not the information was made available via the internet or not)
5) Today: 1-4 In three years: 3-???
6) Nope. There are two farms (one in Washington and one in Canada), and only one person lives on and would be accessing everything only from the farm here (WA).
7) Unfortunately not.
8) Currently Windows XP Home Edition
9) Currently no budget has been laid out yet. Aside from the hosting this is really something we want to put together ourselves. Even if that does mean hitting the books and spending a lot of time learning what we need to put this together.
Even with the multiple locations, it doesn't necessarily have to be web accessible. I thought about Access, but from what little experience I have with it and previously tinkering around, I never figured out a way to include a spot for images with each entry, so I'm not even sure that would be possible (is it? ^^; ). As far as identification (especially concerning the horses. The majority of them are Bays, so often times to tell certain one's apart, there are very little differences in the shape or length of their markings that can't exactly be captured in words in a way they could be identified easier just by looking), images are very important (that and for helping to monitor injuries and the like).
Would it be possible to access an off-line version if it was based on the web, maybe a copy or something?
Friend also say:
"I like Feral person."
Thanks so far RoundBoy and Feral.
It is, but it's a pain. One image per record isn't too bad, multiple images per record quickly gets unwieldly. Between that and the multiple locations issue, it sounds like PHP/MySQL is probably going to be the way to go.
There are ways to synchronize between an offline copy of a database and an online copy. You can build something in PHP to import/export the database to text files and linked images; or you could go online and use one of the many MySQL synchronization tools available for download. Either way you're going to want to get comfortable enough in PHP to actually build the application first and worry about import/export later. Just know that it is possible.
the "no true scotch man" fallacy.
*edit: also, what software for MySQL do you advice?
The first thing to do is understand that the database is the most important bit; not the web site. I know this sounds backwards ("who gives a fuck as long as the website looks OK?") but it's true. If you screw up your database, you're going to get bitten hard on the ass later, and then it might be too late to fix it. Database structures are notoriously resistant to change. If you didn't collect a piece of data you now want from the beginning, you're stuffed. If you want to change which table data is stored, you might be stuffed. This stuff will seriously make you question your sanity.
Don't use MySQL, use PostgreSQL. PostgreSQL is a much more "databasey" program, and used to have a lot more features than MySQL does (although MySQL has caught up with version 5 somewhat), but it's still the better choice. MySQL won favour with large sites because it processed results slightly faster. With what you are doing, it is better to have the database work better.
That said; this is a big project. I'd say you are looking at a week full-time (five working days) to get something that resembles what is being asked, and then another week getting it right. And that's if you know exactly what you are doing, so if you're starting from cold then you're looking at a month probably. You can get plenty of advice on how to do all the web site stuff, but look up the database stuff carefully first. Look up Entity-Relationship (ER) diagrams and database normalisation particuarly, and design the database properly. Then you can hack your way through the PHP/CGI/ASP/Ruby on Rails
I hope she realizes that learning PHP and SQL are not trivial tasks. For all intents and purposes, she's going to be learning a scripting language. Most people would just pay somebody to do this. This is roughly analogous to picking up a hammer and nails to add a wing to your own house instead of hiring a contractor.
the "no true scotch man" fallacy.
Oh shit, I missed that bit
For all that is holy, do not even attempt this if you haven't programmed before. It simply won't work.
Oh, and the flash drive thing: probably not going to work. Unless you did it in Access, which is bad, to access the database you need MySQL or whatever stored on every single machine, plus the scripting language environment and a web server. That's not going to be feasible.
One last thing: don't use Access. Just. Don't. It's a horrible app and a system-hog to boot.
Hm, Lewisham, my friend says, and I quote, "it needs to be a working version, down to being able to view and edit information." is that possible, at all?
On a memory stick? No. It's too difficult. These programs are designed as servers; they're not supposed to be things that can be used "out of the box".
It may be possible to have all these things running off of a memory stick in some portable format if you find instructions on how to do it, but I would be surprised if that was possible, particuarly in a Windows environment.
I'm well aware that learning PHP and SQL are not trivial tasks. I don't expect this easy or magically snap together over night. This whole project is going to be quite extensive, frustrating and time consuming. That's why I'm branching out and asking others more informed than I right now what would be the best route to take to get what I need and it's not going to make it any easier if the key things I need are going to be brushed off to where I sent off down a route that will leave me at a dead end weeks or months from now when it turns out one of the key things I needed weren't plausible in that route (or at all for that matter)
Above all else, the two most important issues really are:
- Transportability of an offline version to be accessed at either farm is highly important and I really want to avoid having to have a server setup at the farm just for that.
- The two other girls I work with who want to be helping maintain the information are about as computer illiterate at they come (as mostly likely any other future employees who might get involved will be). This is why I'm trying to find a solution with as much automation as possible (without sacrificing data integrity) that would include a way for me to create an easy and simple setup for them to access for editing and adding to the information. This is also to help save me time in the future so I'm not the only one working on it and having to do everything manually @w@ (but if it comes down to that, I will).
This doesn't scream "MySQL + PHP" to me. It actually sounds like you'd want something more like a standalone program that just stores plain text files and images. Possibly something like Python with a decent GUI toolkit, storing the data in xml documents (I only skimmed the requirements, but it seems to be mostly text and numbers).
Agreed, but XML and all this jazz is going to make it way too difficult.
If the transportability is more important than power, you really are looking at standalone programs like Access or Filemaker. You will be hard-pressed to get the functionality out of them that you want, but it can be done.
That's awesomely cool! I didn't know it was possible. It must be crazy hacked source or build options
Something better than Access though. Maybe Filemaker.
I agree.
http://www.thelostworlds.net/
Create tables like
HORSE
horse_id
name
sire_id
etc
STAFF
staff_id
name
etc
The only tricky thing would be modeling the sire data.. but if each record contains the id of the horse they sired, it can create a link.. It MIGHT need a seperate table to model all of that though... i need to play with it.
The forms are created easily in the GUI interface, so you can lay them out as you need to. Its not THAT much easier to do VB over php/ mysql ... but its an option.
Librarians harbor a terrible secret. Find it.
And if portability is a requirement; instead of putting it on a memory stick, how about installing it on an old laptop?
the "no true scotch man" fallacy.
Niiice X3 -favorites-
It wouldn't necessarily have to have tohave a full working version on my flash drive to be transportable. We do have a computer at the farm, so if there's a way to work it so that the bulk can be stored on it and just cart back and forth the updated information, that would work too. Unfortunately, the computer at the farm is certainly not some sort of peak performance variety machine @w@
But to answer your question, it's really a mix of both. With it on the web, everyone involved can access it from home and from the Farm in Canada, but that leaves out the farm in Washington, while focusing on physical transportability keeps things in check at my house and at the farm here, but limits everyone else and the second farm.
Your friend has done a lot of good stuff in coming up with requirements capture, some UI design, and a rough guess as to what data is important. My gut instinct is that you could get a software contractor to make this for her within a few weeks at a cost of around $10k. The downside to this approach is that your friend won't be able to make changes herself.
Alternatively your friend can spend a few months trying to figure out how to do it herself and take up to a year to build it. She will then be able to maintain it herself, though it is likely to be strung together by shoestrings and frustration levels will be high.
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
She knows it's not a walk in the park.
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
Exactly.
The only reason to do this project herself is if she already had a desire to learn programming anyway. If she had no desire to learn programming before deciding that she wanted a database, then doing it herself just for the sake of doing it herself is rather silly. As I said previously, it's like picking up a hammer and nails and adding a wing to your own house yourself. Unless you educate yourself on the best practices and established guidelines for building a house, you may end up with something that threatens to fall apart at a moment's notice.
But if the idea of learning to develop web applications sounds fun and exciting to her, and if she thinks it's a skill she might use in the future, then doing it herself is an option.
The very first thing to do if she really wants to do this herself is to buy an introductory book on building databases for the web. I'd say that the place where most beginners screw up is they don't structure their database correctly. I can't emphasize this enough: the challenge here is not just to learn the technical skills necessary, but to learn and follow the best practices as well. Any 12-year-old can build a website, but building it in such a way that won't make you tear your hair out six months later when you need to add a database field (when your previous design decisions that you made at 11:30 pm on a Thursday are no longer fresh in your short-term memory) is harder than it sounds.
Yeah, these are two contradictory goals. I'm not saying you can't do both. It is possible, it just adds to the complexity significantly. You're going to have to set up some way to synchronize the offline copy of the database with the online copy of the database, which, if you don't know what you're doing, can be a recipe for catastrophe.
the "no true scotch man" fallacy.
Double-good, FileMaker has an "instant web publishing" option that will basically throw the database UI up there on the Web for free. No messy coding.
Triple-good, FileMaker has a "FileMaker mobile" version now that basically lets you stick your filemaker database on a mobile device and then sync up later when you're back online.
I built a big FileMaker database back in the 1999 to run a store's point of sale system. They do $3-4 million a year in business and have thousands and thousands of customers in there. They're still using it, or an evolved version of it anyway. Performance is fine.
Seriously, people, I don't understand you sometimes. Simple problem -> simple technology. PostgreSQL and PHP? Are you serious? In FileMaker, I could have a basic database up and running before you even got that stuff installed. The interface would be better than anything you'd devise, too, considering Web browsers are shitty interfaces to databases unless you're a master of AJAX.
*edit: oh, thanks DrFrylock.
I don't, sorry.
And DrFrylock, that sounds awesome.
the "no true scotch man" fallacy.
As someone who has worked with Access extensively I'll say right now that Access is completely inappropriate for this project. You'd have to design a web front end that would access the db using ODBC queries, and then you'd have the classic "Access shits its pants if 10 people make a request at once" problem. If you can trick it into even handling 10 requests at once, it's not exactly designed around the multi-user paradigm. So you'd be setting up session handling and request queuing to make it run reliably, and then it'd be dead slow. And none of those things are easy for someone who's never done any web programming before to get working.
If you're going to use a Microsoft technology for this, SQL Server Express paired with ASP .NET would be the way to go, but then you're still stuck using IIS and I don't believe there's a free way to do that. The MS route is going to be rather expensive, and from what it sounds like, FileMaker's probably a much better choice anyhow.
You're still stuck with the fact that it's not easy for a beginner to wrap their head around how databases "work" and how to set one up correctly, and you're also still quite stuck with the fact that they'll need to figure out some coding to automate the import/export process to work with the memory stick and all that, but it sounds like the most do-able option.
It's still going to take an unreasonable period of time for someone with no experience or education in the field to put together something functional and reliable, though.
CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
That said, Access has undergone a significant redesign for 2007; so much so that many of the criticisms levied here no longer apply as of that version. However, I haven't used it enough to know if Microsoft has introduced an entirely new set of problems (as they tend to do) so I can't recommend it yet.
the "no true scotch man" fallacy.
...
Let us know how she gets on with that later on then O_o