Rate my schema

[Hypotenuse]

Hi all,


I'm just beginning a database for images and am unsure of structure. The idea:

• Each image might show multiple systems (piping systems), e.g. a single photo might show compressed air, steam, and fuel gas
• Each image is related to only one site. Multiple (engineering) projects can occur on the same site
• Each site has multiple areas. It's likely that some areas would have duplicate names in different projects, but that's coincidental. e.g. two separate sites/projects might both have a "Boilerhouse" area
• Each image could relate to multiple areas. e.g. a single photo might show the "Boilerhouse" area, "Cogeneration" area and the "Overall site" area.

It's the two "circular" relationships that I suspect I've got wrong (at "bottom-right" of attached image). I'd really appreciate if someone databasey would comment. There's currently very few records (testing) entered so I don't mind wholesale changes.

[jzwp11]

You say the following:

Multiple (engineering) projects can occur on the same site

Can 1 project cover multiple sites?

[Hypotenuse]

Ha - a thinking man! In theory yes and I did consider that, but in practice it's only occurred for me maybe twice in twenty years so I'm intending to ignore the possibility for simplicity.

[jzwp11]

In theory yes and I did consider that, but in practice it's only occurred for me maybe twice in twenty years so I'm intending to ignore the possibility for simplicity

Technically, if it has occurred, it is no longer a theory, correct? I'll leave the final call to you.

I see a few things I do not agree with in your relationship diagram. I don't thing you need ProjectID in tblImageDetails. Also, I don't think you need the clientID in the project table if the Client is tied to the site. You would still need the siteID in the project table if you keep your scenario simple per your earlier post.

I saw a few other things, so I just decided to lay out the structure as if I were doing it:


If a client has multiple sites:

tblClient
-pkClientID primary key, autonumber
-txtClientName

tblClientSites
-pkClientSiteID primary key, autonumber
-fkClientID foreign key to tblClient
-txtSiteName

A site has many areas

tblClientSiteAreas
-pkClientSiteAreaID primary key, autonumber
-fkClientSiteID foreign key to tblClientSites

Since an area can have 4 extents, that describes a one-to-many relationship

tblClientSiteAreaExtents
-pkClientSiteAreaExtentID primary key, autonumber
-fkClientSiteAreaID foreign key to tblClientSiteAreas
-fkExtentTypeID foreign key to tblExtentTypes
-Extent

tblExtentTypes (holds four records East, West, North, South)
-pkExtentTypeID primary key, autonumber
-txtExtentTypeName

tblImages
-pkImageID primary key, autonumber
-fkAuthorID foreign key to tblAuthors

tblAreaImages
-pkAreaImageID primary key, autonumber
-fkClientSiteAreaID foreign key to tblClientSiteAreas
-fkImageID foreign key to tblImages


tblSystems
-pkSystemID primary key, autonumber
-txtSystemName

Since an image can show many systems and a system can be shown on many images (I assume), then you need a junction table to handle the many-to-many relationship

tblAreaImageSystems
-pkImageSystemID primary key, autonumber
-fkAreaImageID foreign key tblAreaImages
-fkSystemID foreign key to tblSystems



Option 1: A project is associated with only with 1 site (simple approach)

tblProjects
-pkProjectID primary key, autonumber
-fkClientSiteID foreign key to tblClientSites
-txtProjectName
-txtProjectNumber

Option 2: A project is associated with multiple sites

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
-txtProjectNumber

tblProjectClientSites
-pkProjClientSiteID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkClientSiteID foreign key to tblClientSites

[Hypotenuse]

jzwp11: that's fantastic....loads more help than I expected - thank you. Even just seeing the extent types pulled out to another table prompts me to add two more ExtentTypes "dblElevationLower" and "dblElevationUpper" which allows definition of a right cuboid (shoebox) spatial volume (very common to have multi-level plant).

I've done a heap of work with .net/ado linking to other authors db's but have started very few myself - i.e. it will take me time to digest your schema but I've little doubt it's infinately better than mine.

It may give you a warm glow to know that in some commented credit line in some obscure database app in an obscure country (NZ) there will be reference to "JZWP11" - eternal fame is yours!

[jzwp11]

Glad I could help out, and thank you for the "eternal fame"

[Hypotenuse]

Epilogue: I finally got around to redoing the schema. I've always avoided junction tables as they seemed too mysterious, but seeing them used in this context.......I actually think I understand! Easy to see how one image can relate to multiple systems....I scratched my head for a while trying to determine if one image could relate to multiple areas in this schema (spot the n00b). 'Yes' I decide because 1 pkImageID can occur in multiple records in tblAreaImages, each referencing a potentially different pkClientSiteAreaID (with all its linked goodness).

Junction tables are the answer to all of lifes problems!

[jzwp11]

It looks like you have it all worked out! Good luck with your project.