Hi everybody!!
i must create a database for a bed and breakfast!!!!!!
- there are different types of rooms : family, double & simple
- In certains rooms, the client can smoke but not in all rooms
- there is a breakfast on a print car and clients can choose what they want
- There are different kind of menus : Vegans, vegetarians, nut allergies
- In menu, we also can find juice, coffee, tea
- Clients can order menu if there are not expensive
- For foreign clients, the passeport number must be asked
- For the booking, clients choose the type of room, type of menu
- le B&B récompense les fidèles clients

Table Client (ClientId , FirstName, lastName, Adress, Postal Code, Country, Phone, PaiementId)
Table Room (RoomNumber, TypeRoom, Price)
Table BookingRoom(RoomNumber,CLientId, DateBeginning, DateEnd,)
Table Paiment (PaimentId, Type, Date,)
Table BreakfastList?
Table BreakfastClient?
table Price?? I am not sur

What do you think about a such database??
Do you see any problems in this structure?
anything I should have done differently?
anything I could do to improve database performance?

All comments will be appreciated!!
Thanks

1. In case if client can book only one room then you may to merge Table Client and Table BookingRoom and Table Paiment + BreakfastId from Table BreakfastList + passport number for foreign clients + comment from client

2. I suppose you have to add some infomation columns to Table Room (like chair number)

3. I'm not sure that table Price required because of price depends from previous choise set.

Your schema is not normalized enough.
Try this one:


For the part that says:

- Clients can order menu if there are not expensive
- For foreign clients, the passeport number must be asked
- le B&B récompense les fidèles clients

will be things to handle in the core logic, not in the DB.

As for

- In certains rooms, the client can smoke but not in all rooms

I'd put that as attribute of a room, and in the page displaying the booking options, you would need to display it to the customer.

Don't hesitate to ask if you have questions.
I crafted the schema in less than 5 minutes at work (while compiling), so it may have flaws I've missed, but the base should be good.