July 20, 2022 at 9:03 am
Hi,
I have a case where a bus driver can be allocated a max of 5 buses.
How can I enforce this rule in my table ?
2nd question:
Is it better to enforce this rule at backend or frontend ?
Thanks
July 20, 2022 at 12:00 pm
If that is a business rule for your database, you should enforce it in the database. ( mainly to avoid manual mistakes )
ERD:
Drivers - id/name/...
Busses -Ā id/LicensePlate/Capacity/...
DriversBussesAllocation - Date/DriverID/BusID/... --> This is the table handling the relationship ! ( FK to Drivers and FK to Busses )
+ trigger with self-join to count the total number of DriverID per date and fail having count(*) > 5
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution š
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 20, 2022 at 1:05 pm
One way would be to have an assignment # column in the association table -- e.g. AssignmentNumber. Use tinyint. And create a check constraint that limits AllocationNumber to 1 through 5 and allows each combination only once -- e.g.,
CREATE TABLE dbo.DriversBussesAllocationĀ
(Date datetime not null,
DriverID int not null,
BusID int not null,
AllocationNumber tinyint not null
);
CREATE UNIQUE NONCLUSTERED INDEX [ncix_DriversBusAllocationNumber] ON dbo.DriversBussesAllocation
(
DriverID ASC,
AllocationNumber ASC
)ON [PRIMARY]
GO
ALTER TABLE dbo.DriversBussesAllocationĀ WITH CHECK ADD CONSTRAINT [ck_DriverBusLimit] CHECK (AllocationNumber >= 1 AND AllocationNumber <= 5)
GO
ALTER TABLE [syncbak].[Channel] CHECK CONSTRAINT [ck_DriverBusLimit]
GO
-- Plus approprite primary key, indexes
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply