Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2008
    Location
    Seattle, Washington, USA
    Posts
    6

    A writer's market database

    Hello everybody,

    I'm new to these forums, and so this being my first post I thought I'd also introduce myself while also asking for a bit of review/help on a project I'm working on.

    I'm a writer, and I'm creating a database for managing my markets and submissions. I've worked with a couple databases in the past, but I've never created one before, and so I'm going off just a bit of reading on some of the proper techniques for creating a sound database.

    The database I'm creating is in OpenOffice.org's Base. I don't purchase, and neither can I afford, Microsoft products, so I use and try to help out with whatever free software is available. This is a limitation and also a blessing for someone in my field -- that being dirt-poor writer -- so I'm also working with what I have available. Also, from what I understand, Base will make setting up my forms, and reports fairly easy, instead of having to program something, and I can link data from the database to form letters and such that I write in Writer -- all very much like what I understand MS Access is capable of doing.

    Anyway, all that aside, what I'm looking for help on is whether I've managed to get the tables and associations correct with what I've come up with so far, based on the notes and requirements that I made for the database.

    The database is here:
    http://sak.102010.org/workshop/markets_db.tar.gz

    The notes are included in the above archive as a text file, but I'll also include them at the bottom of this post.

    As I mentioned, I've never designed a database before, so any feedback would be greatly appreciated.

    Thanks in advance,

    Sak.

    Writing Markets Database Design


    Publishers

    Publishers should contain specific data related to what the publisher offers and does. It's a company or organization that purchases works by authors for publication.

    Does it allow electronic submissions?
    Does it allow simultaneous submissions?
    What is the average response time (3 months, 6 months, etc.)?
    What type of payment is offered (contributor copy/subscription, per word, per page amounts, etc.)?
    Notes or reminders related to a specific publisher.

    Publishers can have multiple Contacts.
    Publishers can have multiple Publications.
    Publishers can have multiple Contests.
    Publishers can accept multiple different Genres.
    Publishers can accept multiple different Types.
    A publisher requires a contact, even if it's just "Editor"; it doesn't have to be the name of a specific person at the publisher.

    Contacts

    Contacts are people in the industry that are related to either a Publisher, a Contest, or a specific Publication. Direct contacts for submitting works.

    Information needed for a specific contact.
    Name
    Title
    Address
    City
    State
    Country (in the case of international Submissions)
    Postal Code
    Email Address (Contact can have multiple email addresses, i.e. as one for "editor@somepublisher.com" and a personal address for direct contact "joe@ somepublisher.com")
    Phone Number (Contact can have multiple phone numbers, i.e. one for the office, one for their direct line, cell phone number, etc.)
    Notes of any specific reminders related to a particular Contact.

    What Publisher, Publication or Contest is the contact related to, if any?

    Publications

    Publications are the actual printed product from the publisher.

    What's the title of the Publication?
    What's the Publication's schedule (monthly, quarterly, etc.)?
    What Publisher or Contest is the Publication from?
    Which Genres does the Publication fall under? There can be multiple Genres in a Publication.
    Which Types appear in the publication? There can be multiple types in a single publication.

    Contests

    Contests are held by publishers or other public and private organizations. There is typically a publication assoicated with a contest.

    Which Genre does the contest fall into? A contest could have multiple genres associated with it.
    Which Types does teh contest accept? A contest could have multiple types allowable for submission.
    What is the name of the contest?
    When is the submission due for consideration in judging?
    What's the judging date?
    Does the contest allow simultaneous submissions?
    Is there an entry fee? If so, how much?
    What's the prize?
    What Publication is associated with the Contest?

    Genres

    What Genres do I write? (Science Fiction, Literary, etc.)

    Types

    What types of work do I produce? (Poetry, Essay, Short Story, Personal Essay, etc.)

    Submissions

    Submissions are the actual pieces of writing completed that are being sent to a publisher or contest.

    What Genre does the piece fall under?
    What Type of piece is it?
    What's the title of the work being submitted?
    Which Publisher(s) is it being submitted to? There can be multiple Publishers in the case of simultaneous submissions.
    What Publication would it be printed in?
    What is the expected response date for the submission? (i.e. when do I expect to hear back from the Publisher, given their response time?)
    Was the piece accepted for publication?
    Which issue of the publication will the piece be printed in?
    Which rights were transferred for this piece in order for publication?

    Submissions can have multiple Publishers.
    Submissions can have multiple Publications; such as a piece being printed in another publication, from a different publisher, after it's already been published. i.e. First North American rights sold to one Publisher, while European rights can be sold to another Publisher.
    Submissions can be sent to multiple Contests.

    Rights transferred should be a required field, but only if the piece was accepted for publication. How would I do this?

    General Questions:

    Do I not need Foreign Keys that aren't being used? In the case of the Submissions table, there are several that don't appear to be in use, but which are relevant to the information needed for a submission; i.e. which Genre or Type does the piece fall under, which Contest or Publisher was it submitted to, etc.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i was unable to do anything with your "dot gee zed" file

    the quote that you posted looks an awful lot like a homework assignment

    did you have a specific question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Edit: checking his site before I upload...

  4. #4
    Join Date
    Feb 2008
    Location
    Seattle, Washington, USA
    Posts
    6
    Quote Originally Posted by r937
    sorry, i was unable to do anything with your "dot gee zed" file

    the quote that you posted looks an awful lot like a homework assignment

    did you have a specific question?
    Sorry, do you mean that the tarball doesn't decompress properly? Or you don't have OpenOffice.org Base to be able to open the .odb file that it contains?

    This isn't a homework assignment, in terms of anything for any scholastic endeavor. This is something I came up with on my own to keep track of my submissions and markets for my writing. I'm attempting to create a tool that I can use for handling some of the business aspects of my writing career.

    There isn't any specific question, but more just a request for review and comment; just general ones.

    i.e. There are some foreign keys in some of the tables that aren't being used by associations. I'm wondering if it's better to just delete them, or leave them? Also, I don't know if I have all the associations correct. One to many, many to many, etc.

    I apologize again for being so new to this whole process. Thanks again for your support.
    Last edited by Sak102010; 02-25-08 at 21:35. Reason: fixed .odt to .odb

  5. #5
    Join Date
    Feb 2008
    Location
    Seattle, Washington, USA
    Posts
    6
    Quote Originally Posted by sco08y
    Edit: yeah, I'm going soft in the head. That's a school website.
    Well, I'm confused. My personal website is not a school website...

    http://sak.102010.org/

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    No... I don't think it is. The 10210 looked like a course number.

    We get a lot of people posting homework questions here, which is to say, engaging in academic fraud. It gets old after a while.

    Looked over your site and it looks like you really are a writer. I agree with you about All American Rejects, though my reaction was a little less dramatic, more along the lines of slapping my head.

    I did looked at your schema and attached my changes. Many to many joins require an intermediate table with a dual primary key, some other stuff is just a matter taste, for example, I don't bother with an ID for a simple lookup table like Types or Genres.

    You'll probably want to do it differently, and I'm sure Rudy will tear my design apart... if he can figure out how to build OpenOffice with a COBOL compiler.
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sak102010
    Sorry, do you mean that the tarball doesn't decompress properly?
    i don't even know what a tarball is, let alone have the smarts to "decompress" it

    you must be one o' them weirdo mac users or something

    but hey, anybody who quotes korzybski on his web site is okay by me

    ceci n'est pas une pipe, and all that jazz

    any specific question you want to ask, i shall do my best to help you

    p.s. hang onto that domain name, it's valuable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Location
    Seattle, Washington, USA
    Posts
    6
    Haha, thanks guys.

    Sorry about the confusion on the domain. I understand the concern about students trying to trick homework assignments out of you. I have come across a number of posts and sites hiring writers for writing subject essays that they then sell to college students. Not my gig, to be sure.

    Thanks for the suggested modifications, scooby. I hadn't thought of using an additional table to join tables for multiple associations. I actually didn't know that's how you had to do it in OOo, until just after I had made my initial post here and saw some discussion about it on the OOo Base forums. Your example helps clear up some of the confusion I felt about some of the associations.

    Anyway, I'll noodle around with it some more and try to be more specific with my questions going forward.

    Thanks again.

  9. #9
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Sak102010
    Anyway, I'll noodle around with it some more and try to be more specific with my questions going forward.
    I'd suggest actually trying to put some forms together and get some actual data in there. Often times with stuff like Access and Base the limiting factor is whether or not you can get a decent user interface out of it. Lots of nested relationships look cool in the schema but if you have to look up ID numbers manually it's worthless in practice.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Hey Rudy, a "tarbal" is the *ix equivalent of a .ZIP file. The .gz tells you that the file has been compressed using the GZip utility, and the .tar before it tells you that multiple files have been combined into a single byte-stream in the *ix "tar" or tape-archive format.

    I've repackaged the whole bundle as a ZIP file if you are interested.

    -PatP
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, pat

    so the "whole bundle" consists of a text file which was already quoted above, and an ODB file which i can't read

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I don't know why Rudy but I had you pegged as a bit of an open source kinda guy
    I downloaded OO a long time ago and have yet to bother with Base. Their word processor is fantastic though
    George
    Home | Blog

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by georgev
    I downloaded OO a long time ago and have yet to bother with Base. Their word processor is fantastic though
    There wasn't such a thing as Base until version 2, when they embedded HSQLDB. Before that, you could connect to data sources but there was no real app built around it. I haven't really been digging, but I don't think it's part of the published specs yet.

  14. #14
    Join Date
    Feb 2008
    Location
    Seattle, Washington, USA
    Posts
    6
    Yeah, sorry about the "tarball" assumption, Rudy. I'll go with a more standard zip format moving forward.

    Now that I've had more time to work on this project, and going off the suggestions that Scooby offered, I'm stuck again a little bit. Here's the latest version...

    http://sak.102010.org/workshop/markets.odb.zip

    I tried to do what Scooby suggested, in terms of creating the relation tables for multiple relations, but I'm not able to get the same results he was able to. What I came up with seems right, but I'm not sure. For example, in my relationships for Publications, I've got the PublicationTypes and PublicationGenres crossover tables, but the relationships are a bit different than what Scooby did. In my case, there is a 1 to1 relationship between Publication and PublicationTypes, and then a 1 to many relationship between Types and PublicationTypes. Is that the same thing as saying, "Publications can have many types"?

    In Scooby's suggestion, though, there's a 1 to many relationship between Publication and PublicationTypes, and then a 1 to many relationship between Types and PublicationTypes. I'm just not sure how that was achieved, because when I try to do it, I can't get it to work. So is what I've done essentially the same thing?

    I put Scooby's suggested example here for reference too...

    http://sak.102010.org/workshop/markets_sugg.odb.zip

    Sorry, I realize I'm hanging a lot on design questions, and I really need to start creating the forms and putting data in, but it's also a learning process for me as I go, so hopefully you'll forgive all the completely ignorant questions.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's okay, i do realize i'm a microsoft weenie (and not a very skilled one) when it comes to operating systems

    wish i could help you with the odb, but i can't

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •