Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Having a blonde day, can't see the solution though it may be obvious

    Hi guys,

    I'm building a back end system for a hot tub company, where she and (through a user account) the customers can see when they last purchased cleaner, and based off the customer's hot tub size, they know when to purchase more, or the client can email the customer to say it's nearly time.

    For this, I am only using 3 tables

    User - user_id, username, pword, accessLevel, customer_Id
    Customer - customer_id, name, address, contactEmail, hotTubID
    HotTub - hotTub_ID, name, size, dateOfLastPurchase etc

    What I am working on atm is the user registration. As the client already has a database of her customers, I was going to build that up, with their name, address, contact details, email address (to be used as customer's login name for the system) and a reference to their hot tub.

    What I was thinking was, looking at 'users' & 'customers' and finding the entries of 'customers' where 'user.customer_id' != 'customer.customer_id'

    The SQL I used was

    Code:
    SELECT *
    FROM customer, users
    WHERE customer.customer_id != `User_CustomerID`
    However it didn't work. Maybe I'm doing this a long winded and obscure way. Any suggestions?

    Thanks

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Your database structure is a little puzzling. Having the 1:1 relationship between users and customers I can sort of get but in that case you could just merge the tables together. The part that stands out is the 1:1 relationship between customers and the hot tub table. What happens if a customer buys more than one? (Especially over time)

    I'm not clear on what data you are trying to pull out of the database.
    OracleGuy

  • Users who have thanked oracleguy for this post:

    Beironjohn (08-10-2011)

  • #3
    New to the CF scene
    Join Date
    Aug 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oracleguy View Post
    Your database structure is a little puzzling. Having the 1:1 relationship between users and customers I can sort of get but in that case you could just merge the tables together. The part that stands out is the 1:1 relationship between customers and the hot tub table. What happens if a customer buys more than one? (Especially over time)

    I'm not clear on what data you are trying to pull out of the database.
    Thanks for the reply.

    Whilst writing an answer to your post, things have sort of slotted into place.

    I was going about completely the wrong way.

    What I am thinking now is:

    When creating a new user, look at the customer table, find the email address, then look at the user table, if there is not already a username with that email adress, don't show it on the list of customer names during registration

    (the customer will type in their email address and be prompted to create a password during registration, or the client can do it from the admin section herself.)

    Does that make sense? I ask because I am not ovally great at database design :P

    Thanks


  •  

    Posting Permissions

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