newbie want to get basics right

[littleme]

Having searched the net and read a bunch of posts, Ive realised that Ive set up my database wrong. Ive read up on normalizing and so forth but am still a bit usure...

I work for an architects office. Would like to use Access to organize the informations attatched to each room number in a project. Everything depends on this room number. So would end up with hundreds of fields in one table. Is that ok? (I thought it would be hard to over view so split it up in categories and ended up with several 1:1 relationships. not good. right)

I could have everything in one table, except we will probably need to collect data from external consultants (ventilation, electicity etc) who do not have access and who are not connected to our server.

Ideas on how I would deal with these two issues? I want to keep it simple.

 

[MagicMan]

Normalize till it hurts, de-normalize until it works.
You could have hundreds of fields in a table or you could break the table up into logical tables with 1:1 relationships (not totally bad). My general rule is to have all the mandatory data in one table, and data that is optional in logical groups, in other tables. When an optional group exists, it is linked by the relationship, when it does not, no data storage is assigned to that data (where it may take space if not split).
Regardless, data can be extract to Excel spreadsheets, or text files to be emailed to the consultants. When the information is returned from the consultant, an update query can be used directly from the spreadsheet/text file to update the database.
You can also update the information from multiple forms, instead of trying to squeeze everything onto one form. Make the forms freindly and logically group data in each form. Popup forms can be used to update groups of data, while keeping the main form which identifies the project and room active.
Hope this helps
Smiles
Bob

 

[littleme]

hmm...so hundreds of fields are "ok"? Makes things easier in every way. What if I were to keep the tables that the other consultants are meant to fill out separate (since they dont always do as theyre meant to and leave things blank) What happens when records are deleted in "my" table? Are they still left in theirs then?

Is an update query difficult to manage?

And what is meant by parent- child tables? Find lots of references to it but no explanation...

 

[georgedwilkinson]

Hmmm. I'd like to offer a little different advice. This assumes a real-time (OLTP) business system. Normalize, period.

Denormalization creates data anomolies in an OLTP system, period.

Hundreds of fields is a very bad thing.

Parent child tables are the physical representation of the real-world relationship between two types of business data.

An example would be an order (or invoice/quote/purchase order). The order has "header" information (the parent) with the order number, customer, shipping information, special info, etc. and "detail" information (the child) about the items on the order, the quantity, special instructions, etc. By correctly normalizing your database, Access can easily present an order for editing and viewing. By denormalizing, crap breaks.

 

[MagicMan]

If that data is unique and a component of the "Room number", leave it as part of the table. You can extract the data for the consultants from the table. When updating the consultants data using an Update Table function, any deleted records will be written to an Update Error table (created by access) which you can review, confirm, and delete.
The Update Query is not complicated. If you find you need help with that, post another Question to the forum, but you should be able to figure it out.

 

[MagicMan]

George, If you have 300 unique elements with no commonality for one Item, one table with 300 fields is appropriate. Not a desired condition, but to break a table into many tables, linked 1:1 is even less desirable. Now, if the elements can be broken down into logical groups, such as ventilation, electrical, structural, and that group could be retrieved and updated as an entity, then breaking the larger table into logical groups makes sense. I am not sure if that can be done with this set of data.

 

[littleme]

>>"Hundreds of fields is a very bad thing."...

What should I do then?

>>"An example would be an order (or invoice/quote/purchase order). The order has "header" information (the parent) with the order number, customer, shipping information, special info, etc. and "detail" information (the child) about the items on the order, the quantity, special instructions, etc."

Then just to be sure: I do not have a parent-child relationship in my tables as all information is connected to/dependent on the room number, regardless if the info is in categroy electricity or ventilation or whathaveyou?

 

[MagicMan]

Having delt with architects, I understand the vast amount of unique elements/fields you have. Question, how many of these are blueprints or diagrams? If there are a lot, then the access database limit may be a larger concern. I agree with Geroge, hundreds of fields is a bad thing...normally. In many situations, I have had tables with hundreds of fields. Not what I liked, but what can you do when you have hundreds of unique elements?

 

[littleme]

what a luxury to have people answer this quickly. If my co-workers could be as prompt... None of the fields concern blueprints/diagrams. What i need the database for is to keep track of is color schemes, electrical needs, number of sockets, airflow, door widths etc for each room... Up till now we've kept track of all that in a word document...

So i guess im going to combine the tables I have and accept that there are tons of columns/fields.

Any other things I should think about?

 

[neileg]

NO!

Assuming that rooms have broadly the same information requirements, then you should have one record per room. So you end up with lost of records, not lots of fields.

You may end up with tables that look like this:

tblBuilding
BuildingID Primary key
BuildingName
BuildingAddress1
BuildingAddress2
BuildingAddress3

tblRoom
RoomID Primary key
BuildingID Foreign Key link to tblBuilding
ColourScheme
ElectricalSockets
etc

 

[MagicMan]

Question, are the hundreds of fields associated with one room, or one project?

 

[littleme]

>>"So you end up with lost of records, not lots of fields."

Got it.

>> "Question, are the hundreds of fields associated with one room, or one project?"
One database per project. Tons of rooms per project. Tons of fields per room. (Gonna try to eliminate less important fields)

 

[Rabbie]

>>"So you end up with lost of records, not lots of fields."

Got it.

>> "Question, are the hundreds of fields associated with one room, or one project?"
One database per project. Tons of rooms per project. Tons of fields per room. (Gonna try to eliminate less important fields)

As a general rule Access tables should be tall and thin not wide and low. Many newcomers to Access come from Excel and have a spreadsheet mentality. You need to get away from that. Look at some of the sample databases and see how Normalisation is done.

Remember tables are for storing data and queries are for extracting it.

Forms should be based on queries and used for adding/amending data.

As you say you are newcomer to Access it will be useful for you to look at Allen Browne's website

 

[georgedwilkinson]

So i guess im going to combine the tables I have and accept that there are tons of columns/fields.

This is like the worst idea I can think of. There is absolutely no reason whatsoever to have a table with anything approaching 100 fields. This approach will cause you grief for years to come.

 

[georgedwilkinson]

One database per project. Tons of rooms per project. Tons of fields per room. (Gonna try to eliminate less important fields)

No need to eliminate fields. Your database should store the data you need whether it's important now or just corollary.

 

[MagicMan]

I believe you have 2 tables:
Table 1 = Project
Table 2 = Room
Each project has many rooms.
Each room has hundreds of fields
Is this correct?
Sorry to go over and over this.

 

[Rabbie]

I suspect that you need extra tables in a 1 to Many relationship with your Room table records. Without knowing the details of your design to date it is not easy to know exactly what you need so is it possible for you to post a copy of your design.

 

[littleme]

This is like the worst idea I can think of. There is absolutely no reason whatsoever to have a table with anything approaching 100 fields. This approach will cause you grief for years to come.

Now I'm getting confused. (sorry) If splitting the tables up with 1:1 is not recommended and neither is having all information related to a room record in one table then what are my options...?

 

[MagicMan]

I agree, Could you create one table with a test project (remove all the names and replace with test client, test addr1, etc.) and a room with all it's elements (again changing the data to 1" ... we just really need the field names). This will allow us to determine if normalization can be apllied. The result will probably be a database layout with normalized tables.
Smiles
Bob

 

[littleme]

Ok... here's what I have so far. Its really only the beginning of it. Havent wanted to set the whole shebang up without understanding the principle of it. Also posting a PDF of the desired end result (according to my boss) where all the titles under the headings are some of the necessary fields for each discipline. (I know its in Swedish but I hope you get the idea. The headings are in English though) All the information is specific for that RoomNumber. And there will probably be more fields under each header than there are now.