setting maximum cardinality with sql


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: setting maximum cardinality with sql

  1. #1
    Join Date
    Apr 2009
    Posts
    1

    setting maximum cardinality with sql

    I'm trying to write the sql for a database for a sample video rental store. It has to be so that a customer can rent only 10 videos maximum at a time. Each video rental stores the date of the rental, and the date it was returned. How do I add this constraint in?

    let's assume using sql, the tables are defined as:
    Code:
    create table member(memberID integer primary key);
    create table rentals(member integer foreign key references member(memberID),
    video integer foreign key references video(videoID),
    dateRented datetime not null, dateReturned datetime, 
    constraint pk primary key (member,video));
    create table video(videoID integer primary key);
    now I'm thinking that to represent a video still checked out, I add an entry to the rental table with the returnedDate as null. How do I make it so only a max of 10 videos can be rented out at once per member?

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What are you using for your front end?

    That is where I put the 10 video code check.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Apr 2009
    Posts
    3

    Response

    You could do it in your front end, but it would seem that the preferred way to do it now would be to throw an exception from the database that the "front end" would pick up. Hence, I believe doing it in your database.

    I am not sure what flavour of database you are using, but I would consider using a trigger. The logic is:


    Trigger fires
    - does the customer have 10 or more videos on hire?
    - if yes, then throw exception
    - if no, allow processing.

    Check the documentation for your Db provider and you should get something that you could use.

  4. #4
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    Quote Originally Posted by RespeckKnuckles View Post
    It has to be so that a customer can rent only 10 videos maximum at a time. How do I add this constraint in?
    I think that the question is regard the Business rules.
    Therefore, keep in mind that the user should be able to change this rule later (i.e. 8 or 12 video max, instead of 10).

Similar Threads

  1. OBDC and SQL Server
    By angelia in forum Database
    Replies: 0
    Last Post: 07-01-2003, 04:42 PM
  2. Replies: 2
    Last Post: 05-18-2003, 11:16 PM
  3. Replies: 1
    Last Post: 04-02-2003, 01:10 PM
  4. treeview without using ActiveX
    By Aaron Coombs in forum ASP.NET
    Replies: 2
    Last Post: 09-26-2001, 04:29 AM
  5. SQL API setting in VB
    By Jon Zhang in forum VB Classic
    Replies: 0
    Last Post: 07-05-2000, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center