how many fixtures per floor?

[jammerculture]

Name: Database structure.png  Views: 15  Size: 27.9 KB  ID: 10449" class="thumbnail" style="float:CONFIG" />



I am trying to create a database to organize work in high rise construction. There are many suite types and fixture types. Each suite type has a fixture package to be installed. Each floor has a differing number of suites of a given type on the floor. I want to be able to query the database to tell me how many of each fixture per floor, per group of floors etc. My problem is in making the tables that relate the fixtures to the suite types and the suite types to the floors.

[orange]

I don't see a table for fixtures, perhaps more description of what each of these things is would help.

Just a note on your table and field names.

You should avoid having spaces in table and field names. Use only alphanumeric and underscore "_" characters.

Also, for ease of use as you proceed, do not use ID as the PK in each table. It will get messy and confusing especially when communicating with others. Use names such as FloorID in tblFloors, HeaterPackageID in tblHeaterPackages ...
it just makes the field easily identifiable. Not mandatory, but a good practice.

[jammerculture]

thanks for your reply. sorry, your right I need to keep the terms consistent. tblLighting should be tblFixtures perhaps. I am so new to this stuff that even asking questions is difficult. As well, my overall plan is quite large so I am attempting to break it up into manageable pieces. Let me start over.

The object of this piece of the database is to define what suites exist on what floors, and what packages (lighting fixtures, heating fixtures etc) go with each suite. The goal is to manage material and workflow in a highrise building. tblSuiteType will define the suite types that exist in different combinations on different floors. tblSuiteCombo will define the combination of suites. For example levels 3-10 have 4 x A suites, 4x C suites and 4 x D suites. so in tblFloors the record for level 3 will contain suitecombo A, wheras the record for level 11 will contain suitecombo B.

A similar set up occurs on the left of the database. For example suite type A has 4 x lighting type 1, 2x lighting type 2 and 1 x lighting type 3.We can define this as Lighting package A in tblLightingPackage. My question is how to design tblLightingPackage and tblSuiteCombo to have these relationship to tblLightingFixture and tblSuiteType? I had setup tblLightingPackage with fields fixture 1, fixture 2, fixture 3 etc, but it seems to me that there should be a better way. As well I don't think this helps me with the goal of being able to query the database "how many fixtures of type 1 are on levels 3-10"

PS, I understand the importance of naming conventions and consistency, and I have been reading many links you have provided in other threads on normalizing, design etc, so I am already in your debt even before you answered my thread

thanks for the help

[jammerculture]

a few more questions on naming. I was aware that spaces were not good in table names but did not know about fields. As far as PK goes, the lighting fixtures come with letter and number designations. I have used these as my PK (renamed) fixtureID. Is this a good idea? Or should I just let the autonumber fixtureID be the PK and then have a designation field? The same with floors. In many highrises certain floors are skipped, for example level 13. This can make determining how many suites between level 4 and 15 more difficult then it needs to be! My original thought was to make the PK floorID be the actual floor number, but maybe I shouldn't.

What do you think?

[orange]

A PK for most databases is a "tool" for the use of the underlying database management system(Access in this case). The PK should have no meaning to you.
If you need some identifier for your use, then make one, but Access is happy with autonumber PK (which he tries to build by default.

This explanation is just meant to say that using the autonumber PK supplier by Access is a good idea, But the autonumber PK field in an of itself should not be something you have to report on or attempt to adjust. Name it so it's not confusing and leave if for Access's use.

Don't know if you read the Entity Relationship Diagramming article at Rogers Library, but is is a good example of a good approach and shows the steps from initial description through to normalized tables with relationships. It is most important to get your "Business" described in meaningful terms so you can work with rules/specifications to build and test your model before you get into the innards of Access and syntax.

http://www.rogersaccesslibrary.com/T...lationship.zip

[jammerculture]

so weird, am actually reading it right now, and already decided to implement that approach.

sometimes I post too early in the process

I require a material and workflow tracking database. I want to track information about fixtures to be installed in a high-rise. A high-rise has many floors. Each floor has suites on it. Each suite can be one of a number of different suite types defined by floorplan. Each floor, therefore has a combination of different numbers of different suite types. Each suite type has light fixtures (also heaters, receptacles etc but let’s start simple). Each light fixture is one of a number of different fixture types which are defined by fixture type, location, mounting, lamp type, number of lamps, and trim. Each suite has a certain number of each fixture type to be installed, called a lighting package.
Ok so the italicized is where I am getting hung up. I do not know how to create the tables that will tell me that level 3 has x number of each suite type, and that each suite type has y amount of each fixture type. The attached was my quick attempt to display what I'm after with Excel but even then it's awkward and I'm only dealing with light fixtures! Once I add in heaters, receptacles, etc then Excel is obviously not the proper tool!

[orange]

I tried putting a model together--not real sure about it -- it may be useful.