Results 1 to 8 of 8

Thread: creating tables

  1. #1
    Join Date
    Jan 2010
    Posts
    4

    creating tables

    sorry about the double post. Posted in the wrong forum earlier
    I am designing a database that will store urls found in a mini search engine program. The idea seems simple but after creating my tables ( oh I am using SQL server management studio express 2005 with vb.net) I had lots of problems and am resorting to going back to the basic design and not coding on the fly.
    I need to store the search items entered with a unique key. Basically for each search item there will be many posts and many urls. But for each url, there can also be many posts.
    I need to store the data in a searchItem table, post table and url table. This is what I have thus far;

    table urls
    urlId int Unchecked
    queryId int Unchecked
    postId int Unchecked
    url varchar(450) Checked

    table posts
    postId int Unchecked
    queryId int Unchecked
    urlId int Unchecked
    postLink varchar(450) Checked
    date varchar(50) Checked

    table searchitems
    queryId int Unchecked
    searchItem varchar(50) Checked

    Any ideas really appreciated. I must be really dumb not to be able to figure this out.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cengineer View Post
    I must be really dumb not to be able to figure this out.
    figure what out?

    was there a question in there somewhere? i must be really dumb because i couldn't see it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Do you mean posts as in forum posts? I am going to proceed on that assumption.

    Search
    SearchID int,
    SearchItem varchar(50)

    URL
    URLID int,
    URLLink varchar(450),
    SearchID int

    Post
    PostID int,
    PostLink varchar(450),
    PostDate varchar(50), -- unsure why you want this or what it represents
    URLID int

    The URL.SearchID is a foreign key to Search, Post.URLID is a foreign key to URL. There is no reason to repeat the query ID in the post table because you can always find it by joining to URL via the URLID.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    imagine a forum where you couldn't tell what date a post was made...

    why, this one right here was made last week
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    4
    Thank you sir, I will try and implement this and for the previous member, pardon me, I should have made the question more explicit.

  6. #6
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    You have to forgive Rudy. Many of here are old and grumpy. Rudy is grumpier than most because he lives in Canada.

  7. #7
    Join Date
    Jan 2010
    Posts
    4
    That's fine. I do have one more question and am not sure if this is the appropriate forum. I took your advice and simplified the tables.

    Search
    SearchID int,
    SearchItem varchar(50)

    URL
    URLID int,
    URLLink varchar(450),
    SearchID int

    Post
    PostID int,
    PostLink varchar(450),
    PostDate varchar(50), -- unsure why you want this or what it represents
    URLID int

    I did keep the date since it is relevant since the date will help when querying for posts based on search date.
    Anyway, the SearchID, which is auto incremented, needs to be inserted into the other tables as foreign keys. Now I found a post about using SCOPE_IDENTITY and tried this and it works great. However, if I had a SearchItem requested which is already in the Search table, to run fresh searches on it, it will return 0 or null from the SCOPE_IDENTITY.
    How would I then get the id for search item that is being used and already present? Should I use a new INSERT INTO(SELECT...) type statement and what would this be? Do I include this in the stored procedure that already returns the SCOPE_IDENTITY?

  8. #8
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    To get the ID for a search item that is already present in the table just SELECT SearchID from Search WHERE SearchItem = '<whatever>'.

    I am not sure exactly what you are getting at since I don't know what your stored procedure does. Just relax and work through the issue, you should be able to figure it out.

Posting Permissions

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