I think it's fully normalised but I could do with a second opinion, not 100% sure on table names yet

Address table 
adressid(FK)
userId(FK)
shopId(FK)
street(varchar)
city(varchar)
postcode(varchar)

shop details table 
shopId(PK)
userId(FK)
mondayOpen(time)
mondayClose(time)
tuesdayOpen(time)
wednesdayOpen(time)
wednesdayClose(time)
thursdayOpen(time)
thursdayclose(time)
fridayOpen(time)
fridayClose(time)
saturdayOpen(time)
saturdayClose(time)
sundayOpen(time)
sundayClose(time)

shop images
imageId(PK)
userId(FK)
imagePath(varchar?)
imageType(blob?)

shop reviews
reviewId(PK)
userId(FK)
shopId(FK)
review(varchar)
rating(int?)

    I don't know if it would make any meaningful difference w.r.t. normalization, but I feel like I want the store hours in a separate table. It might just be 5 fields:

    shop_hours
==========
shopHoursId (PK)
shopId (FK)
weekday (enum: 'sunday', 'monday', . . .)
status (enum: 'open', 'close')
time (time)

    Like I said, though, I don't feel strongly about that since the number of days in a week are not likely to change -- though it might make it easier to indicate if a store is closed that day, simply by not having any entries for that day.

    On a separate note, I'd recommend not storing any images as database blobs; instead storing them in the file system and only storing the file name (and possibly the directory if needed) in the DB. Otherwise every time you need to supply the image to a http request, you have to pull it from the DB.

    NogDog Like I said, though, I don't feel strongly about that since the number of days in a week are not likely to change

    I can't think of any shops that schedule their opening hours on a cycle of anything other than a week.

    It will though make it a bit fiddly to ask day-relative questions like "is the store open right now?" or "what time does it close tomorrow?" since you can't parameterise the name of the column to query. Mind you, you'd still need to figure out what day to search under anyway.

        For generic "shops", I'd say that there's a pretty good reason to separate the daily opening hours by week into another table: some shops are restaurants that open for lunch, close during the afternoon, then re-open for dinner. Your original design where you assumed that shops open once a day cannot model that.

        On the other hand, I don't think it is useful to separate the opening and closing times into different rows. I would see "shop_hours" as modeling time periods denoted by the opening and closing times, rather than a particular "shop opening/closing time", which is what NogDog's design looks like.

        laserlight I would see "shop_hours" as modeling time periods denoted by the opening and closing times

        I might go with that -- just had an urge to separate it out from the shop table. 🙂

            Note that a shop's opening hours may span midnight; that would involve either notionally "closing" the shop at midnight and then immediately "reopening" it, leading to a situation similar to laserlight's; or keeping the interval between opening and closing contiguous and refuting the idea that a shop's opening and closing hours are on the same day.

            Shops that are open around the clock, of course, are free to close-and-immediately-reopen at whatever time of day suits their processes.

              dalecosp
              Pay checks? It's a bit weird, if you're not getting a brown envelope behind the bar something is wrong

                  Write a Reply...