Table Design (1 Viewer)

SpentGeezer · Sep 30, 2010

Greetings,

I am creating an assets database. Each asset has approx 100 fields, all 1:1. Should I make one table, or try to break it down into several tables linked by Asset ID? There are no 1:M relationships, so there is no real reason to make more than 1 table. Is the massive 100 field table poor design?

Thanks,
Josh

John Big Booty · Sep 30, 2010

What is the information held in your 100 field for each asset?

SpentGeezer · Sep 30, 2010

It is all maintenance information, so we have:

1.Location information - approx 10 fields
2.) Defect information - approx 10 fields
3). Work Order information - approx 20 fields
4). Status and works under construction details - approx 10 fields
5.) Completions Details - approx 20 fields
6). Costing - approx 20 fields.

Actual asset and financial attributes are stored in 2 seperate tables.

See I could break it down into 6 tables (as above) but is there really any point with all 1:1? Also the data is in stored in ArcSDE (ESRI SQL Server database) and is querried by GIS software and a GIS web page, so keeping it in 1 table makes it alot easier on that side of things.

However, Ignoring the GIS side of things what is best practice in DB design for this scenario.

SpentGeezer · Sep 30, 2010

I guess as it is a progressional thing, to save storage space they should be seperate.....An asset could be sitting in the construction phase for months, with all the completion and costing being empty until completion..

John Big Booty · Sep 30, 2010

If any field holds data that recurs it should (in most cases) store a foreign key value that refers to a table that hold the list of possible responses. This eliminates duplicate responses with spelling inconstancies which makes querying the field difficult in the extreme. Have a read up on database normalisation.

SpentGeezer · Sep 30, 2010

John Big Booty said:
If any field holds data that recurs it should (in most cases) store a foreign key value that refers to a table that hold the list of possible responses. This eliminates duplicate responses with spelling inconstancies which makes querying the field difficult in the extreme. Have a read up on database normalisation.

Familiar with data normalisation. I have ensured data integrity by using limit to list comboboxes in the form..

John Big Booty · Sep 30, 2010

I hope your not using Table Level lookups

SpentGeezer · Sep 30, 2010

John Big Booty said:
I hope your not using Table Level lookups

No way!! The user can't see any tables, it's all entered through forms, all forms full screen, using MDE. No list filters, just plain old combo list populated by the lookup table.

John Big Booty · Sep 30, 2010

I guess I'd stick with your one table, from what you've said.

gemma-the-husky · Sep 30, 2010

spentgeezer, i thing you are misunderstanding normalization.

i dont think you can possibly have 100 bits of data per asset, that are all independent of each other, or that could not benefit by having some lookup tables.

Your assertion that you have 100 fields, all 1:1 is meaningless in this context.

Can you provide a list of these 100 different attributes, so we can make appropriate comments

jdraw · Sep 30, 2010

Work order, defect, location, status,costing... seems you are supporting some kind of process(es). Perhaps you could tell us in plain language what your "business" is and see if multiple tables make sense.
I tend to agree with Dave that 100 attributes - independent - don't make much sense without the business context.

SpentGeezer · Oct 1, 2010

jdraw said:
Work order, defect, location, status,costing... seems you are supporting some kind of process(es). Perhaps you could tell us in plain language what your "business" is and see if multiple tables make sense.
I tend to agree with Dave that 100 attributes - independent - don't make much sense without the business context.

Plain English: my "business" is local council, management of roads and footpaths (Assets)
When a tree grows big the roots can damage footpath or road. This = defect. A pothole is a defect. These have to be fixed and ALL information recorded due to public litigation (Joe Blog tripped over a crack in the footpath, was the defect handled in a way that complies with Auspec Guidelines?) There is a lot of information about the defect and the PROCESS from inspection through to works completed...approximately 100 fields worth! All mandatory and all related to the one defect. I don't think you really want me to type in all 100 fields do you?

I apologise for my Noobism, and I will stick with the one big table, makes it easier for my GIS work anyway. Cheers.

gemma-the-husky · Oct 1, 2010

so presumably you have columns for each different task

inspection, workdone, workchecked, worksignedoff, workpaidfor, etc, each of which has a matching column for datedone, who did it, who checked it, etc etc. Although the DATA may not be the same, this is very similar to repeating groups of information, and repeating groups should be managed by a subtable

I would have thought there was a real opportunity to put all of these separate tasks into a sub-table linked to the incident.

this might make some things you do easier.

for example, lets say you decide you need to store some more information about another process related to each incident. At the moment you need to amend your table and forms each time to include the data columns. No doubt your entry form is now massively complex, multi-tabbed etc etc.

With a structure that managed each process as a sub-event, you wouldnt need to change anything. Just add another process to the process table, and everything works automatically

I thought all this was what you were asking in your initial posting.

Table Design (1 Viewer)

SpentGeezer

Pure Noobism

John Big Booty

AWF VIP

SpentGeezer

Pure Noobism

SpentGeezer

Pure Noobism

John Big Booty

AWF VIP

SpentGeezer

Pure Noobism

John Big Booty

AWF VIP

SpentGeezer

Pure Noobism

John Big Booty

AWF VIP

gemma-the-husky

Super Moderator

jdraw

Super Moderator

SpentGeezer

Pure Noobism

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread