April 11, 2016 at 8:58 am
Hi,
I got a very small (10 tables) database for DVD rentals... The frontend was initially made to work with touch screens on tills, no problem with that...
Now the same system is being proposed to work on a set of three stores sharing the same database, all three have the same DVD's.
My question would be, what's the best way to deal with this change in the scheme? should I just add a new column to the DVD's table to specify the store (1,2,3) and add three copies of the same film or maybe triplicate the DVD's table and have one per Shop? Any other approach you can think of?
Regards
April 11, 2016 at 9:10 am
I think it's a bad idea to assume the existing physical design is correct (unless you know that it was modeled very carefully logically first and then the physical design was based on that logical design).
Instead, you should go through a new, entire logical design process, just as you would for a new database, ignoring existing physical elements like tables , indexes, etc.. Just list all the needed data attributes and logically model them. Go thru at least third normal form or Boyce-Codd normal form.
Then do a new (i.e. starting from scratch) physical design based solely on the new logical design. It's very likely the new physical design will be different, possibly noticeably different. Finally, map the existing physical data into the new physical structure.
For such a small db, the modeling won't take too long (naturally it will take longer if this is your first/nearly first try at logical modeling). And, yes, it is worth it. Otherwise you will run into things later, after you'll already re-done the physical data, that will cause you far more headaches and re-work.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 11, 2016 at 9:39 am
Are you sure your model makes a difference between Title/movie and DVDs/copies? Those are different entities.
April 11, 2016 at 10:26 am
Luis Cazares (4/11/2016)
Are you sure your model makes a difference between Title/movie and DVDs/copies? Those are different entities.
Well, since the original database was meant to work with single copies of the films there was no 'Inventory' table to define the amount of copies for any single film...
When a film is rented a BIT field on the DVD's table is marked (IsRented), when returned this changes.
Current structure look like:
+---< DiscountGroups
¦
+---< Customers
¦
+¦-> Orders
¦
+---< Order Items
¦
¦
+---< Movies
¦---< GenreList
¦---< MovieTypes
+---< Ratings
Rebuilding the database is really a last resort since that would mean working with the frontend and thats something I'm trying to avoid (time constraints)
April 11, 2016 at 10:47 am
sys.user (4/11/2016)
Luis Cazares (4/11/2016)
Are you sure your model makes a difference between Title/movie and DVDs/copies? Those are different entities.Well, since the original database was meant to work with single copies of the films there was no 'Inventory' table to define the amount of copies for any single film...
When a film is rented a BIT field on the DVD's table is marked (IsRented), when returned this changes.
Current structure look like:
+---< DiscountGroups¦
+---< Customers
¦
+¦-> Orders
¦
+---< Order Items
¦
¦
+---< Movies
¦---< GenreList
¦---< MovieTypes
+---< Ratings
Rebuilding the database is really a last resort since that would mean working with the frontend and thats something I'm trying to avoid (time constraints)
If the front end is calling stored procedures instead of queries, there's no reason to apply large changes if a database redesign is made. Anyway, you'll have to make some changes as you still have to manage the stores in the front-end and the back-end.
It's your decision, you can face the problems now or you can deal with the accumulated problems later.
April 11, 2016 at 10:49 am
A year ago, someone else asked a question about a Movie Rental Database. Could this be the same school assignment?
April 11, 2016 at 11:02 am
Luis Cazares (4/11/2016)
A year ago, someone else asked a question about a Movie Rental Database. Could this be the same school assignment?
Yes it is, I was just too lazy to use the search it happens when your 12...
April 11, 2016 at 11:14 am
Modify the database. Anything else you do is going to be bandaid that creates technical debt. Technical debt, like any kind of debt, adds up and will disable you eventually. Use as little as possible.
For the design, the DVD itself should be one thing and then a mapping of DVD to store, many to many.
And yeah, before others get all worked up, there is a lot more that could be done.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 11, 2016 at 11:29 am
I decided to go with 2 new tables 'Stores' and 'Stock'
Stores if quite simple StoreID and Name
Stock with some data looks like:
================================================
StockID | MovieID | StoreID | IsRented
================================================
1 | 1 | 1 | 0
2 | 1 | 2 | 1
3 | 1 | 3 | 0
4 | 2 | 1 | 1
5 | 2 | 2 | 1
6 | 2 | 3 | 0
Minimal changes to the FE need to be implemented... I understand the implication of going with the 'easy way out' but trust me, this really is a small database, it would process 6 records a day tops.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply