Relationships between tables in SQL server 2014

Transact-SQL
1

Hey,

I got the following tables:
1: Branches.

2: RentalCarDetails.

3: Car Details.

4: Car Category.

5: Users.

I'm in the middle of a project (Just started to learn) and having little hard time with this topic.
For instance, I need that if someone orders a car, this car won't be available and remove it from the available cars. The same applies to the other tables.

If someone can help that would be highly appreciated!
Thanks.

2

You don't have to remove it, you can create a colum that mark the car rented or not rented. For example a column name IsRented can be defined with the data type bit that accept only 1=true and 0=false

3

The "Available Cars" query could include an OUTER JOIN to the RentedCars table and then use that relationship to EXCLUDE from the AvailableList any car that is in the Currently-Rented-out data set.

You could also use an IsRented column, but it then becomes very important that that column is correctly updated at all times (i.e. if a car is RETURNED and the IsRented column does not get set to FALSE (software bug, whatever) then you have 100-shades-of-grief!! sorting that out.

The IsRented column will query faster than "Is this car in the Currently Rented-out Cars data set",and if/when you have a lot of data that may well be a killer. The IsRented column approach will scale, to larger size, MUCH better. The sub-query "Is this car in the Currently Rented-out Cars data set" is self correcting - just take the car OFF Rental and it is immediately available again.

4

Need more details on "in the middle" of a project.

Have you completed a logical table design without regard to a physical design? If you have not, give yourself the most gain possible by STOPPING THE RUSH TO CODE and go back and do a proper, logical design first. This doesn't have to take that long, but it has to be done.

Sorry, had to say that, even though you'll ignore it and instead do hundreds of extra hours of rework of the table "design" (alleged) and code.

But at least be consistent and mindful of business terms in the table names.

  1. ALL table names should be singular or plural, not a mix.
  2. Is "RentalCar" the same as "Car"? I guess not, but what is the difference? What "details" relate to the "car" but not the "rentalcar"?
  3. You don't have "users" renting things, they are "customers".
5

I resemble that remark !!