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:
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?
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);
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
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:
- 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.
I think that the question is regard the Business rules.
Originally Posted by RespeckKnuckles
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).
By angelia in forum Database
Last Post: 07-01-2003, 04:42 PM
By zicq in forum Database
Last Post: 05-18-2003, 11:16 PM
By hamid reza in forum Database
Last Post: 04-02-2003, 01:10 PM
By Aaron Coombs in forum ASP.NET
Last Post: 09-26-2001, 04:29 AM
By Jon Zhang in forum VB Classic
Last Post: 07-05-2000, 12:33 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL