My comments intermixed below :-)
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 09/01/2004 12:30:19 PM:
> While I currently have about a dozen tables, I'd like
> to throw this one up for any opinions, suggestion,
> warnings, things I may not be saying. It's not a
> started entity - schema diagram but should be clear
> for the purposes of the email.
>
> "Table: Posts" (innodb)
> ------------------------------------------
> PostID (auto inc, int) set to PK
> ------------------------------------------
> Entered (timestamp)
> ------------------------------------------
> StartP (timestamp) (user decision)
> ------------------------------------------
> PLen (Int) (user decision)
> ------------------------------------------
> VenID (int) (Not FK'd to the VenTable though, don't
> see the need) Session variables and joins keeping them
> related
> -------------------------------------------
> Ind (varchar) based on user choice from menu provides
> IndID from static table
so call this IndID. As a matter of personal preference (very personal) I
name my FK fields (even if I don't actually create a FK to enforce the
relationship) like "tablename_columnname". So I would call it Ind_ID (or
whatever your other table is called)
> -----------------------------------------
> City - (varchar) based on user choice from menu
> provides CitID from static table
and this CitID or City_ID
> -------------------------------------------
> State - (varchar) based on user choice from menu
> provides StatID from static table
ditto: StatID (State_ID?)
> ---------------------------------------------
> Term - (varchar) based on user choice from menu
> provides TermID from static table
ditto: TermID (Term_ID?). However, wouldn't you need to support multiple
terms per posting? This would be a situation where normalization requires
you to split this value into its own table like:
CREATE TABLE Posts_Terms (
Posts_PostID int,
Term_ID int,
Primary Key(Posts_PostID, Term_ID),
KEY(Term_Id)
) TYPE=InnoDB, COMMENT 'This table holds a list of terms used to
identify a post for searches'
The multi-column primary key will prevent any single "term" from being
applied more than once to any one "post". The second index would speed up
searches to find which "post"s are associated with specific "term"s.
> ------------------------------------------
> Rate - (varchar) User text input
> ----------------------------------
> Contact - (varchar) right now they are pulled dynamic
> fields from the Ven Table, defaulted into form for
> Posts and put into this table
??? - I have no clue what the Ven table has in it or how you defined
fields that change their data types and defaults ("dynamic fields" ?) so
your comment makes less than no sense to me. Don't confuse your web/UI
design with your data design. They may be symbiotic but they are
definitely NOT the same.
> --------------------------------------------
> Contact Email - (varchar) same as above - I'm starting
> to see an issue here, meaning, possible bad design),
> My strategy was that one company may have an account
> with various users sharing the one contact name and
> password. Not sure.
??? - again I can't understand the context of your comment. I know from
previous messages to the list that you are working on a job seekers web
site but you don't seem to have a well defined idea of "who" needs to know
"what" about "whom".
> --------------------------------------------
> One more insignificant field.
>
so why mention that you have it? If it is truly insignificant, don't add
it to the table.
.......
May I suggest that you temporarily stop development and go through a bit
of basic object modelling? Start by defining the different users (also
known by the UML term "actors") of your system and what you want them to
be able to do. I think I can name 3 but you may have more: JobSeekers,
EmployeeSeekers, and Administrators (please create your OWN names that
better fit YOUR business model).
A JobSeeker is someone who wants to be found by a EmployeeSeeker.
They will want their profile/resume/CV to be accurate, online, and
searchable.
EmployeeSeekers are those people who want to find people
(JobSeekers) they can hire to fill certain positions. They need to be able
to search all of the profile/resume/CV(s) of the JobSeekers on your site
and initiate contact with them if they meet their hiring criteria.
Administrators are superusers (like you) who need to be able to
change anything about everything associated with the site. Most of what an
Administrator needs to do will probably be accomplished through off-site
(not-web-based) methods like direct editing of your data tables using
MySQL tools and scripts. However, you may want to develop certain
web-based tools to help with list management (terms, cities, states, etc)
and other related processes (like creating and disabling JobSeeker
logins). The trade-off to consider is "will a little time invested in
building a web page for "process x" save me lots of time compared to doing
this by hand (over and over)?".
........
Just from my basic, simply-worded text description, you can start to get a
picture of not only what data you need to store (and maybe what not to
store), but also what features and processes your site needs to support,
what processes your data structures need to support, and where your
current prototype design may be lacking. I have identified (or implied
through my description)not _only_ a few actors but some objects (JobSeeker
account, profile/resume/CV,...), and several processes (Add "profile" to
system, Edit a "profile", Make a "profile" available for searching, etc).
Take the time early on (like now) to flush out the vast majority of the
details of your "application" using this kind of text-based design
description. Keep your process descriptions broad and simple at first:
"JobSeekers need to be able to: Login, Add resumes, Edit existing resumes,
Post resumes, Remove posted resumes,...." You can go back and add the
details during later revisions. When it comes time to describe your
business objects (data storage), the more detailed you can be about what
information each object contains (again, start with broad descriptions and
refine through later revisions) the less time you will need to spend
during the development phase creating and revising your database design.
For example, what qualities an EmployeeSeeker would think a JobSeeker
needs to have in order to be eligible for a certain job is probably NOT
something you need to keep in your database. Those would be the terms they
will use to search by, right? This would be object information (a
"SearchQuery" or other usefully named object) that you process and handle
through your UI and convert to SQL queries against your database but store
it there. Similar (but not the same) information would be necessary to put
into your database if you decide that you are also keeping lists of
JobOpenings (another object I had not previously considered). So now you
need to consider if you need to design something to hold JobOpenings in
your database or you could decide that that is a future feature and ignore
it until you have time to work on it.
See how working this business process out on paper BEFORE you start coding
can save you lots of real time when it becomes time to actually create the
database and write the code that supports it? You won't spend nearly as
much time second-guessing what you need the database to support because
you already thought it through on paper.
Keep us in the loop. This is turning into a wonderful teaching situation
and you will probably get loads of free help (on the data design at least)
as it's all going to help many others on the list, too.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
