A writer's market database

Sak102010 · #1 (**permalink**) 02-25-08, 19:16

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.

Quote:

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.

r937 · #2 (**permalink**) 02-25-08, 20:19

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?

sco08y · #3 (**permalink**) 02-25-08, 20:29

Edit: checking his site before I upload...

Sak102010 · #4 (**permalink**) 02-25-08, 20:30

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.

Sak102010 · #5 (**permalink**) 02-25-08, 20:32

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/

sco08y · #6 (**permalink**) 02-25-08, 20:39

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.

r937 · #7 (**permalink**) 02-25-08, 20:46

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

Sak102010 · #8 (**permalink**) 02-25-08, 20:56

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.

sco08y · #9 (**permalink**) 02-25-08, 21:12

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.

Pat Phelan · #10 (**permalink**) 02-25-08, 23:12

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

r937 · #11 (**permalink**) 02-26-08, 01:06

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

gvee · #12 (**permalink**) 02-26-08, 04:13

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

sco08y · #13 (**permalink**) 02-26-08, 23:35

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.

Sak102010 · #14 (**permalink**) 02-27-08, 19:36

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.

r937 · #15 (**permalink**) 02-27-08, 19:55

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

Internet Services

Web Development

Digital Marketing

Consumer Tech