November 20, 2007 at 4:25 pm
Which is why you sometimes end up with tables or columns, or whatever object that doesn't meet your naming conventions. All you can try for is consistancy, and most likely live with the exceptions.
November 20, 2007 at 4:27 pm
russ dalton (11/20/2007)
- bit fields are bad. i've been bitten by them in the past and avoid them now. It's too easy to take something that should be True or False and find something else. As an example, I worked in a finance firm and we had a bit field on Living. Pretty simple, alive or dead. Right? Nope. We had to add a field for unknown if we couldn't contact the person any more. A whole new field. It's just not worth saving those 7 bits.
Couldn't you just make the field nullable? Seems null would perfectly handle your condition: no value is available.
November 20, 2007 at 6:10 pm
Hi all, I must admit after the first 4 pages of comments it got a bit harder to read all the detail. 🙂
Along the lines of what Carla suggested...
Carla Wilson (11/20/2007)
By the way, I like Russ's idea to make the Owner column a varchar datatype, and store the name of the person you borrowed the book from, even though this was not the original intent for this column. Putting myself into Steve's shoes, if I were borrowing a lot of the books I want to review (as opposed to buying all of them), this would be a great way to keep track of who to return them to!
I started thinking myself, then JJ B popped up with where I was heading...
JJ B (11/20/2007)
What if you want a list of books by an author and you had miss-typed an author's name in a free-form field? What about the user-friendly approach of providing a drop down box so that author names don't have to be fully typed out?
Wouldn't it be great to have a reference table holding a list of owners so you don't have to type in "Fantasyville Library" dozens of times hoping to spell it right every time?
Here's a dreamy concept, would libraries expose your personal borrowing details through web services so you could have details sucked in to the DB automatically populating return dates?
I hope Steve's next installment isn't too far away.
November 20, 2007 at 6:26 pm
Theon Kimball (11/20/2007)
Wouldn't it be great to have a reference table holding a list of owners
And that then leads to the question of borrowing a book from a guy who is an author 😀
Maybe we need to buy in a CRM system? :hehe:
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
November 20, 2007 at 8:05 pm
Maybe we need to buy in a CRM system?
Yep... then run it on two Cray's... one for the interface, one for the Data Warehouse 😛 Maybe get an extra one to run the SAP and financial reports :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2007 at 8:07 pm
noeld (11/20/2007)
Definitely was not expecting this much feed back. Apparenly Steve has a nack for "debates".Good one Steve!
Yup. Steve dumped some high-quality chum into the shark tank today. But it's good to see all of this hashed out. I am sure everyone who reads this thread will take away something they didn't know before.
K. Brian Kelley
@kbriankelley
November 20, 2007 at 10:28 pm
First off regarding requirements, this is a personal opinion, but here goes.
Steve has provided ample requirements based on the scale of his project to prototype a preliminary system.
I say this because, although more requirements may be useful, something of this size does not need a large development team, nor does it need to go thru rigorous design testing, prior to implementation. Having said this I can comfortably state that the system specified will not be mission critical, and therefore can be implemented using alternate means to gather requirements.
There are times when speed, and/or budget constraints do not allow for full-blown requirements, analysis, and design phases. In those instances prototyping and asking specific questions help you gather the additional information needed to develop and stabilize a design. There are also simple things that can be done within a design that will allow it to be extendable at a later date.
----------------------------------------------------------------
Couple of design styles I prefer using --
1. All tables have a server generated identity primary key, the debate for this vs. natural keys is long and disturbingly useless, both have their strong points and both have their weaknesses. Lets just say this is my preference after years of prototyping systems with minimal requirements.
2. All tables are plural and fields within the tables are singular. Reason a table contains a collection of items, and a record within the table specifies an item within this collection.
3. All table have an insert date, which is populated using the getdate function --- even with small tables I have found this to be extremely useful at a later date when trying to analyze data and need the start date of an event. --- When did an error start happening because someone inserted a new value into a type table?
For example: When did we start using this status and how many records have been affected by this new status?
4. All tables have a deactivation date this allows for records to be programmatically removed from the active set of records displayed to an end-user, while maintaining the record for future analysis if needed. The date fields also work wonders when auditors want to see how your records changed.
5. I do not use bit fields due to future possibility of an Y/N value becoming maybe.
6. Dates are always specified as DateTime simply because this allows for cleaner code later.
7. I use underscores as a separator when naming tables and fields just a personal preference, because it is easier for me to read and removes ambiguity.
8. Another field I usually use is a notes or reference field which allows for free text to be entered regarding a specific record. I did not do it here yet, because I wanted visibility to the core values first.
The following is simply structure; as for data types I will work on that next just need to see how the tables would be laid out first. Nor am I looking at performance, especially since Steve does not foresee this as being a large database and currently Steve is stating he will be the only user. This may change however if he wants to control who is accessing his site, or starts to allow others to post their book reviews in his database also, perhaps for cash if his site becomes popular 🙂
Books
-----------
Book_Id
ISBN
Title
Date_Added
Date_Deactivated
Authors
-----------
Author_Id
Prefix ---- This can also be a lookup if needed (Mr., Ms., Mrs., Dr., etc..)
First_Name
Middle_Name
Last_Name
Suffix ---- This can also be a lookup if needed (M.D., III, DBA etc...)
Date_Added
Date_Deactivated
Book_Authors
-----------
Book_Id
Author_Id
Date_Added
Date_Deactivated
Book_Images
-----------
Book_Id
Image_Location_Id
Date_Added
Date_Deactivated
Image_Locations
-----------
Image_Location_Id
Location_Type_Id
Image_Location
Date_Added
Date_Deactivated
Location_Types
-----------
Location_Type_Id
Location_Type
Date_Added
Date_Deactivated
Images
-----------
Image_Id
Image_File
Date_Added
Date_Deactivated
--- I am choosing to do it this way so that a file folder can be used to contain the images without putting an extra load on the database... also in the event that Steve would like to store images within the database I can simply add an image_type of "Database" with the image_location value defined as the Image_Id, which would then become a foreign key to the images table. (oh the nightmares the DBAs are having with this one)
Book_Statuses
-----------
Book_Status_Id
Book_Id
Status_Id
Owned
Date_Added
Date_Deactivated
Statuses
-----------
Status_Id
Status
Date_Added
Date_Deactivated
Book_Reviews
-----------
Review_Id
Book_Id
Date_Added
Date_Deactivated
Reviews
-----------
Review_Id
Review
Review_Date
Rating
Date_Added -- this can be different from the review date since the record can be technically added at a much later time and review date will be post dated to the actual day the review occurred.
Date_Deactivated
--------------------------------------------
1. Will you be tracking reviews performed by others?
2. Do you need or want to keep track of who actually owns a book if you do not own it yourself? Is there any other data needed on books that you do not own?
3. Do you foresee granting write access to this database (employees, colleagues)? If so how do you foresee controlling this access?
4. Will the website have any permission controlled areas or would you like to know who is accessing your reviews?
November 20, 2007 at 11:09 pm
first off sorry for the double post but few extra questions:)
1. Will you need categorize the books being reviewed i.e hardware, software?
2. Will you need to categorize the reviews i.e academic, professional?
3. Will you be publishing the reviews elsewhere and would you like to keep track of such an occurrence?
4. Do you foresee selling your reviews or time to write reviews?
5. You mentioned having an image that will click thru to an amazon page, do you need to track this? (metrics showing how much interest a review generates.)
November 20, 2007 at 11:43 pm
Andrew_Webster (11/20/2007)
"book" v "books"... hmmm.Anyone else's take on this...?
I always name my tables plural and my columns singular. Table name is Books, the column that contains the book's title is called Title.
I'm not fanatical about it, is just the way I do things.
And none of that prefix mess. I hate seeing a db where all the tables are named tblSomethings. It's in the from clause, hence it's either a table or a view, and the metadata's easy to look up to see which.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2007 at 12:27 am
Dear friends,
I liked the way of thinking of jomobono.
I have come up with a little tiersome approach for creating relationship tables (data tables). I keep a minimum of 7 columns in table.
ColumnName - Datatype
IsDeleted - Bit
Created_By - whatever
Modified_By - whatever
Deleted_By - whatever
Create_TS - datetime
Modify_TS - Datetime
Delete_TS - Datetime
Thanks.
November 21, 2007 at 2:19 am
Jwalant Natvarlal Soneji (11/21/2007)
Dear friends,I liked the way of thinking of jomobono.
I have come up with a little tiersome approach for creating relationship tables (data tables). I keep a minimum of 7 columns in table.
ColumnName - Datatype
IsDeleted - Bit
Created_By - whatever
Modified_By - whatever
Deleted_By - whatever
Create_TS - datetime
Modify_TS - Datetime
Delete_TS - Datetime
Thanks.
But surely such a method only keeps track of the last time a row was modified.
You have added all these fields to keep a partial audit history. If you are going to the effort of creating a generic solution that can be reused, why not make it complete.
Add a proper audit trail of all modifications. Plus, don't just track who changed something and when, but also record what was changed.
And yes, this is going way beyond what is required in this system.
November 21, 2007 at 7:45 am
November 21, 2007 at 8:23 am
tgarth (11/21/2007)
Is anyone familiar with the phrase "Project Creep"?Tom Garth
"Mission Creep" "Project Creep" "Scope Creep" "Feature Creep"
I've heard them all used interchangeably, but they all mean the same thing. The project is going beyond its original scope.
Yeah, I agree with you, for "a personal website for book reviews" the feature list for the website seems to be growing fast.
But for now, I'd still say that this "project" is still in the idea phase so all the ideas are good. I'd say its better to have an idea on the table and choose not to pursue it, rather than never even considering the possibility.
Kris
November 21, 2007 at 9:06 am
I think the most notable issue is the lack of solid requirements (or understanding of the stated requirements). This is the biggest problem in any project. Clearly the author states that only he will be supplying reviews, but grasshopper suggests a separate review table, "what was reviewed by whom and when". What's a flaw and what is scope creep? I do agree with grasshopper's suggestion though to split the review to it's own table, it is its own entity, and does allow for flexibility as the scope creep happens.
I definitely agree with an authors table for data consistency and eliminating redundancy.
Viewing 15 posts - 61 through 75 (of 96 total)
You must be logged in to reply to this topic. Login to reply