SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Puttin together a SQL DB

    Can I post my DB Structure for review and suggestions? Is this a safe thing to do? Normally most people don't have access to read or view the names and datatypes of databases, and before I posted mine for suggestions, I wanted to know if it's a wise thing to do.

    I mean, the system will be in ASP and the Connection String and Database will be password protected, but I just want to be safe. Once I get an answer to this, I'll post the structure. (this is a new DB, no data yet)

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, isn't the best representation of a db structure some sort of diagram?
    (=don't post sql script to create the db, post an image)

  3. #3
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, I sketch things out in text first (which is what I have) since I'm not too familiar on using SQL 7 to set relationships and what not. I won't post any SQL code, just table/field names, their data types, whether they are primary/foreign keys, Nulls Allowed, their Precision sizes, and examples of data that can go in each field, etc. (as well as my notes on "this does that, that does this")

    Would that be safe to post for review?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yeah, go ahead -- "table/field names, their data types, whether they are primary/foreign keys, Nulls Allowed, their Precision sizes, and examples of data that can go in each field" sounds okay to me

    diagrams are nice but people often mis-identify the cardinalities on them

    it's especially difficult to represent null foreign keys on a diagram too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aaron.martone
    ...I'm not too familiar on using SQL 7 to set relationships and what not.
    Here's an article for you
    Database Diagramming with Visual Studio 6.0 and SQL Server 7.0

  6. #6
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Damn thee Jofa! *runs from the MSDN site* you must be a developer more than you're a designer images/smilies/smile.gif The msdn site has been the epitome (to me) in HORRIBLE site content from a designer's aspect. I'm sure to a developer it's HEAVEN, but I can't read a darn thing without gettin confused there... Heh heh heh... Anyways, here's the DB structure and my ideas.

    Code:
    tbl_Portfolio
     fld_PortID   PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Scope	   VARCHAR  PRECISION:8000 NO NULL  "html/asp website"
     fld_Type	   VARCHAR  PRECISION:8000 NO NULL  "internet,identity, etc."
     fld_Title	   VARCHAR  PRECISION:8000 NO NULL  "Ultima V:Lazarus"
     fld_Image_Large	  VARCHAR  PRECISION:8000 NULL  "/images/port_l_001.jpg"
     fld_Image_Small	  VARCHAR  PRECISION:8000 NULL  "/images/port_s_001.jpg"
     fld_Link	   VARCHAR  PRECISION:8000 NULL  "http://www.u5lazarus.com"
     fld_Description	  VARCHAR  PRECISION:8000 NO NULL  "Description goes here."
     
    tbl_Client
     fld_ClientID  PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Name	   VARCHAR  PRECISION:8000 NO NULL  "Chuck Jones"
     fld_Address	   VARCHAR  PRECISION:8000 NO NULL  "123 Place St."	   
     fld_City	   VARCHAR  PRECISION:8000 NO NULL  "Townseville"
     fld_State	   VARCHAR  PRECISION:8000 NO NULL  "Florida"
     fld_ZIP		VARCHAR  PRECISION:8000 NO NULL  "12345"
     fld_Phone	   VARCHAR  PRECISION:8000 NO NULL  "(123)123-1234"
     fld_Fax		VARCHAR  PRECISION:8000 NULL  "(321)321-4321"
     fld_Email	   VARCHAR  PRECISION:8000 NULL  "cjones@att.com"
     fld_Username	  VARCHAR  PRECISION:8000 NO NULL  "chucky"
     fld_Password	  VARCHAR  PRECISION:8000 NO NULL  "KSJD(U@O#JKSJD(U@OJEKSJDSKU(@#"
     fld_AccountID  FOREIGN KEY  VARCHAR  PRECISION:8000 NO NULL  "1"
     fld_AccountStatus	 VARCHAR  PRECISION:8000 NO NULL  "Open"
    Code:
    tbl_Account
     fld_AccountID  PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Date	   SMDATETIME PRECISION:4  NO NULL  "January 1, 2003 12:00:00 AM"
     fld_Payment	   VARCHAR  PRECISION:8000 NO NULL  "Credit Card"
     fld_XCreditCard	  VARCHAR  PRECISION:8000 NULL  "XXXX-XXXX-XXXX-1234"
     fld_CheckNumber	  VARCHAR  PRECISION:8000 NULL  "123"
     fld_Amount	   VARCHAR  PRECISION:8000 NO NULL  "50.00"
     fld_Description	  VARCHAR  PRECISION:8000 NULL  "Made payment for services"
     fld_Balance	   VARCHAR  PRECISION:8000 NO NULL  "0.00"
     
    tbl_Project
     fld_ProjectID  PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_ClientID  FOREIGN KEY  INT   PRECISION:4  NO NULL  "1"
     fld_ProjectName	  VARCHAR  PRECISION:8000 NO NULL  "Ultima V:Lazarus"
     fld_Type	   VARCHAR  PRECISION:8000 NO NULL  "internet"
     fld_Status	   VARCHAR  PRECISION:8000 NO NULL  "open"
     fld_Description	  VARCHAR  PRECISION:8000 NULL  "Progress coming along nicely"
     fld_Financed	  VARCHAR  PRECISION:8000 NULL  "50% paid - final due on completion"
    tbl_News
     fld_NewsID   PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Type	   VARCHAR  PRECISION:8000 NO NULL  "news" or "article"
     fld_Title	   VARCHAR  PRECISION:8000 NO NULL  "We're Open For Business"
     fld_Date	   SMDATETIME PRECISION:4  NO NULL  "January 1, 2003 12:00:00 AM"
     fld_Blurb	   VARCHAR  PRECISION:8000 NO NULL  "We're Open"
     fld_Content	   VARCHAR  PRECISION:8000 NO NULL  "This is the content."
     
    tbl_Tickets
     fld_TicketID  PRIMARY KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Status	   VARCHAR  PRECISION:8000 NO NULL  "Open"
     fld_DatePosted	  SMDATETIME PRECISION:4  NO NULL  "January 1, 2003 12:00:00 AM"
     fld_ClientID  FOREIGN KEY  INT   PRECISION:4  NO NULL  "1"
     fld_Subject	   VARCHAR  PRECISION:8000 NO NULL  "My new account"
     fld_Content	   VARCHAR  PRECISION:8000 NULL  "How do I start a new account?"
     fld_AnswerContent	 VARCHAR  PRECISION:8000 NULL  "It's easy"
    
    Some key points.

    Storing Credit Card Numbers? Yes/No? MD5 Encrypted?

    Enabling 1-1, 1-Many, and Many-Many Referential Integrity. How's it done? Through a SQL Tool?

    Will doing the above step enforce that if I delete one record, anyrecords "linked" to that one will be removed as well (same for updating)?

    On a sidenote, I've made the login system SQL-INJECTION proof (if not proof, significantly reduced the success rate) Any detected characters result in an error before the login credentials are even turned into an SQL Query.

    BTW. My login process is rather odd...

    1. UN/PW boxes created via ASP Include

    2. When data is submitted to them and pressed enter, the POSTed info is sent to the same page.

    3. The ASP include checks to see if a username and password were included in the Request.Form collection.

    4. If yes, then it knows user is trying to login.

    5. Perform an SQL Injection test, checking username and password for characters such as "+, -, --, ,, ', ", insert, xp_, select, /, and \" and if it finds it, aborts the login process.

    6. If everything is legit, we first set a SESSION VARIABLE called CANLOGIN to TRUE and then it uses MD5 to hash the password and sends itself using GET to login_user.asp?un=USERNAMEVALUE&pw=MD5HASHEDVALUE

    7. the login_user.asp page checks to see if the SESSION VARIABLE CANLOGIN is TRUE or not (incase people just goto login_user.asp and start supplying their own values) and if it is not, login process is aborted. If it is, then the SQL statement is built and if a user is found and credentials match (PW's are stored on database in MD5 hashed format) then the user is logged in, if not, sent to a page with error notice. Then the Session Variable CANLOGIN is set to FALSE. Rather lengthy, but do you see any fault in it?

    Anyways, if you need me to explain the structure, let me know; you may have better ways of designing the DB (you probably do) that I can learn from. I'm looking for MAXIMUM performance from SQL DB hits that are no larger than 1-3 table calls and 1-2 joins.

    Thanks for input.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    jofa, unfortunately that article does absolutely nothing for me because i don't have that software

    at the stage aaron's at, the "tool" approach to modelling would actually be a detriment

    aaron, noun_thanks prep_for art_the noun_layouts

    do yourself a favour and ditch the tbl_ and fld_ parts of your names

    the only benefit -- which i hasten to say is overwhelmed by the drawbacks -- for using that naming scheme is that it's difficult to accidentally name something with a reserved word

    now, let's move on to your design

    you have a number of points which indicate that you are keen to move on to the coding phase of your project, but you should stop, take a deep breath, and redesign your structure before doing anything else

    according to the placement of your foreign keys,
    - a client has multiple tickets
    - a client has multiple projects
    - an account has multiple clients, and may have a different status for each client!
    - portfolio stands alone
    - news stands alone

    are those the 1-to-many relationships you were thinking of?

    rudy

  8. #8
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    aaron, noun_thanks prep_for art_the noun_layouts
    Huh? lol.

    Quote Originally Posted by r937
    do yourself a favour and ditch the tbl_ and fld_ parts of your names

    the only benefit -- which i hasten to say is overwhelmed by the drawbacks -- for using that naming scheme is that it's difficult to accidentally name something with a reserved word
    Yeah, that was the main reason. Readability and not using reserved names. Can you tell me what the drawbacks are? Do they take significant more time to hit on a DB with such long names?

    Quote Originally Posted by r937
    now, let's move on to your design

    you have a number of points which indicate that you are keen to move on to the coding phase of your project, but you should stop, take a deep breath, and redesign your structure before doing anything else

    according to the placement of your foreign keys,
    - a client has multiple tickets
    - a client has multiple projects
    - an account has multiple clients, and may have a different status for each client!
    - portfolio stands alone
    - news stands alone

    are those the 1-to-many relationships you were thinking of?

    rudy
    Yes, a client can have many SUPPORT TICKETS (for getting help when they have problems)

    Yes, a client can have multiple PROJECTS (It's a web development firm, and I want them to have the ability to monitor all projects)

    No, the accounts thing sounds wrong. It should be that EACH client has Multiple Accounts. What I'm using the Accounts Table for is like this. I can see an error so far (thanks) that I need to add a fld_ClientID to the tbl_Accounts

    Suppose there were 2 clients. And ClientA gave me a check for $500.00 (THANKS CLIENTA!) I would use my admin panel to create a new Account record with this info

    AccountID : (autonumber, lets say 1)
    Date : (todays date)
    Payment : Check (the type of payment made)
    XCreditCard : (left blank because credit card not used for payment)
    CheckNumber : 123 (number of check)
    Amount : 500.00 (amount of the check)
    Description : To pay off my account
    Balance : 50.00 (the new balance of the account)
    ClientID (added) : 1 (which is ClientA's tbl_Client.fld_ClientID)

    Now ClientB makes a payment; new record

    AccountID : (autonumber, lets say 2)
    Date : (todays date)
    Payment : Credit Card - Visa (the type of payment made)
    XCreditCard : XXXX-XXXX-XXXX-1234
    CheckNumber : (left blank because credit card not used for payment)
    Amount : 250.00 (amount of the credit card deducted)
    Description : Takin care of my debt
    Balance : 0.00 (the new balance of the account)
    ClientID (added) : 2 (which is ClientB's tbl_Client.fld_ClientID)

    And lastly, ClientA makes one more payment

    AccountID : (autonumber, lets say 3)
    Date : (todays date)
    Payment : Check (the type of payment made)
    XCreditCard : (left blank because credit card not used for payment)
    CheckNumber : 124 (number of check)
    Amount : 50.00 (amount of the check)
    Description : To pay off THE REST of my account
    Balance : 0.00 (the new balance of the account)
    ClientID (added) : 1 (which is ClientA's tbl_Client.fld_ClientID)

    So when ClientA logs into the control panel and looks at his ACCOUNT, I'll do a SQL statement (not perfect, I know) like

    SELECT * from dbo.tbl_Accounts WHERE dbo.tbl_Clients.fld_ClientID = '1' ORDERBY dbo.tbl_Account.fld_Date ASC

    And it would output

    1. 3/3/2003 - Check No. 123 - $500.00 - To pay off my account - Balance $50.00
    2. 3/4/2003 - Check No. 124 - $50.00 - To pay off THE REST of my account - Balance $0.00

    That's what tbl_Accounts is to be used for. So yeah I'll need a ClientID in that.

    Portfolio and News will stand alone. Portfolio will automatically just show images and associated text on projects I've worked on from a database. News will do likewise, in which I can enter new posts and they'll come up on our front page. No user interaction with those. Admin only.

    Great info guys. Database stuff is sweet 8 I remeber using Access 2000 to create 1-1, 1-M and M-M relationships, just ever did it in SQL7.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    drawbacks for using fld_ and tbl_ are that readability suffers

    okay, remove account FK from client, add client FK to account, and bob's your uncle

    good luck and holler if you need sql help


    rudy

  10. #10
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    jofa, unfortunately that article does absolutely nothing for me because i don't have that software
    at the stage aaron's at, the "tool" approach to modelling would actually be a detriment
    Well, you don't need Visual Studio, the diagramming works exactly the same way in Sql Server's Enterprise Manager (actually, the tool you use from VS is the same diagram pane as in Enterprise Mgr, I think)
    And if you're creating an MS Sql 7 db, then you have the software Enterprise Mgr, don't you?

    Tool approach to modelling a detriment? I don't agree, isn't a join line between two tables easier to comprehend than sql statements like "create table blah (..., some_column int foreign key references another_table(some_column), ...)"

  11. #11
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You're both fired.

    Ok, cool, so I'm in SEM (SQL Enterprise Manager) and I see the DIAGRAMS. IS that what I want to use to enforce Referential Integrity?

    Also, how important is it to use DEFAULT VALUES when you're in TABLE DESIGN mode? I take it if you do a record insert and don't provide data to those fields, it uses the defaults; so use it accordingly to that?

  12. #12
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, using Diagram maker now. Some points.

    The only tables that are Referential are Account, Client, Project and Tickets.

    I BELIEVE when a link is made, it's read that the side with the KEY can have MANY with the side of the INFINITY sign.

    If that's true, it's all good so far. Right now it shows:

    ONE Client can have MANY Accounts (true)
    ONE Client can have MANY Projects (true)
    ONE Client can have MANY Tickets (true)

    But a stumper comes from the Client and Account tables. Let me refresh to you their structure.

    Client
    Client.name
    Client.address
    Client.city
    Client.state
    Client.zip
    Client.email
    Client.phone
    Client.fax
    Client.accountID

    Account
    Account.accountID
    Account.clientID
    Account.entryDate
    Account.payment
    Account.xCreditCard
    Account.checkNumber
    Account.amount
    Account.description
    Account.balance

    Anyways, I have Accounts's Account.accountID related to Client's Client.accountID. But my question is, does the Account.clientID need to be related to the Client.clientID? I'm having a hard time visualizing this.....

  13. #13
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    LOL. No wonder I was so confused. I don't NEED Client.accountID

    Crikey! This database stuff is funky. The search for Account entries belonging to that Client are handled via searching for the CientID IN the Accounts. Ah, to answer one's own questions.

    Thanks for the help guys.

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aaron.martone
    I BELIEVE when a link is made, it's read that the side with the KEY can have MANY with the side of the INFINITY sign.
    Ehrm, no... The key end of the line is where the primary key is, the infinity end of the line is where the same values occur as foreign keys

    Ehrm, again, wait a minute, that's exactly what you meant


Bookmarks

Posting Permissions

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