Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: Need help ogranizing a simple SQL Schema

    I'm writing a very simple printer monitoring application in VB.NET. My application actually requires the user to authenticate using a Passcode everytime he/she attempts to print. I am using SQL to store data about the Printers being monitored (my app queries the DB to discover what to monitor), the Passcodes, and the number of pages printed.

    I am very new to SQL concepts, but I can write commands to use the SQL Data provider and can do very basic things. However, I do not know very much about relationships and so forth and am on somewhat of a tight deadline. I need the database to do this:

    Contain the Passcodes; contain the Printers (by name), contain data about how many pages each passcode printed on each printer given a date range.


    As I said I am a novice with SQL so I made 3 tables, Passcode, Printers, and JobsData (where each row is a job containing the date, passcode, printer used, and pages printed). I know what I have now is garbage. I would really like to take advantage of a relationship or something. Basically I am just asking for suggestions or (well I'm sure this is childishly simple for DB expert) just the proper way I should do this. Thanks in advance, I really appreciate it.

  2. #2
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    see sample databases

    Checkout the Northwind or Pubs sample databases that come with SQL Server. They have examples of relationships. You basically need a primary key for each table. To join tables A & B you put a foreign key in table B that references the primary key in table A.

  3. #3
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    You have done everything correctly.
    For example:

    create table [User](
    [ID] int IDENTITY(1,1) PRIMARY KEY,
    [Name] varchar(100),
    [Password] varchar(100),
    )
    go

    create table Printer(
    [ID] int IDENTITY(1,1) PRIMARY KEY,
    [Name] varchar(100),
    )
    go

    create table jobs(
    [User_ID] int,
    Printer_ID int,
    JobDateTime datetime,
    NumberPrintedPage int,
    CONSTRAINT FK_User FOREIGN KEY ([User_ID])
    REFERENCES [User]([ID]),
    CONSTRAINT FK_Printer FOREIGN KEY ([Printer_ID])
    REFERENCES Printer([ID])
    )
    go

  4. #4
    Join Date
    Jan 2005
    Posts
    3
    Wow, you both have been very helpful! I looked at Northwind and have learned a lot and that code you posted mwolf, works great. =) My only question now is how can I use the relationship made between User_ID, Printer_ID and the Jobs table? I want to create stored procedures and so forth that will allow me to display, let's say between date X and date Y, how many pages SomeUser printed and on each printer (I will add a paper cost too the Printer table) and how much it cost and things like that. Before when I had no relationships I could only come up with messy hacks that would display this information but many using the relationships I can make a more proper simpler way of displaying this data (I'm using .NET if it helps). Thanks everyone, very helpful!

  5. #5
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You use the relationship by "joining" the table within a SQL statement.

    -- Declare variables to hold your Criteria

    declare @Username varchar(100),
    @MinDate datetime,
    @MaxDate datetime

    -- Define the values

    set @Username = 'John Smith'
    set @MinDate = '20040101'
    set @MaxDate = '20041231'

    -- Execute SQL by JOINING the three tables together

    select u.Name, p.Name, sum(j.NumberPrintedPage)
    from User u
    JOIN Job j on j.User_ID = u.ID
    JOIN Printer p on p.ID = j.Print_ID
    where u.Name = @UserName
    and j.JobDateTime between @MinDate and @MaxDate
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

Posting Permissions

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