Fog Creek Software
Discussion Board




Database table/design problem

I have a database program that I use to manage and keep track of clients including properties that I have inspected (I am also a building inspector/consultant) over the years. As of this time, I am hoping to eliminate my current NCR field forms by implementing additional tables and form views in my database program to allow me to collect and enter data while in the field using a tablet PC. As of this time, the problem is not one of creating a table or form view to enter my observations relative to each system but rather one of creating a table or tables to enter general information specific to each system. For example, given ten hypothetical systems, the number of general info fields required for a particular system varies from two to ten and remains static for each property inspected. If I elect to use one table for this purpose (to enter general info), the database will always contain or generate a number of fields with no values. Furthermore, this only serves to add more empty fields or space as the database grows larger for subsequent properties inspected. On the other hand, I could create a separate table for each of the ten systems. In this way, one only needs to add the necessary fields for each system in which event there are no blank values/fields in the database. The 'Observations' table mentioned above then becomes a detail table for each of the System's general info tables in the database. I have a third option, but for sake of brevity, I've decided to forego the explanation at this time. Any comments or suggestions regarding this subject matter from those experienced in database design are most appreciated.

Thank you,
Marty Potokar

Marty Potokar
Tuesday, June 10, 2003

Data modelling - i personally love it, but it can be difficult to know just how far to go when normalising a system

Ive tried to interpret your requirements and came up with something like

(this is where you would list your 10 hypothetical systems)
Table: System
Cols: SystemID - int
SystemName - varchar(80)
SystemDescription - varchar(255)

(defined fields of comment for each system)
Table: SystemField
Cols: SysFieldID - int
SystemID - int (FK to System.SystemID)
SysFieldName - varchar(80)
SysFieldDescription - varchar(255)

(the table where you can put your comments, based on field and system)
Table: SystemFieldValue
Cols: SysFValueID - int (simply for a PK)
SysFieldID - int (FK to SystemField.SysFieldID)
SysFieldValueComment - text (here is where you would make your general comments),

i hope im close. there is another option with the above scenario, and that is if some of the Fields you describe your systems with are common between Systems, you would remove the SystemField.SystemID col and FK, create a SystemFieldMapping table to map fields to system then add  a FK in SystemFieldValue to the SystemFieldMap record pertaining to the system and field you are commenting on


anyway, hope i understood you properly, if not, let me know and i'll see what else i can suggest. Some of this stuff can really screw with your head

Dan G
Tuesday, June 10, 2003

How about if you created a table with columns like attributename and attributeValue, with a foreign key like PropertyCode that links the row to the specific property it belongs to?

That way each property has 'N' rows in this table, with each row containing one attribute name and value pair. To make maintenance easier you can create views that return data in a multicolumn way. (if you're using an SQL database, that is)

Deepak Shenoy
Tuesday, June 10, 2003

This datamodel may work:

create table system_name(
id_system              number not null,
name                    varchar2(50) not null,
description            varchar2(100) not null
);

create table data_property (
id_system              number not null,
id_property            number not null,
name                  varchar2(100)
);

create table  property_value (
id_value                number not null,
id_property            number not null,
comment_text        varchar2(100),
value                    varchar2(100)
);

This way you can have n properties (fields) per system, with just one table to store them in. If your users would look at/work with only one system at a time, you could possibly create a layer of views ontop these tables to only display the fields/value pairs for the currently selected system.

Patrik
Tuesday, June 10, 2003

It sounds like you are considering a commercial application here.

You are also wondering about having different table designs for each customer?

Don’t do it. Your support issues will become a night mare. You want ONE code base, and you want ONE table design that works for all customers.

To ignore the above means you can all of a sudden wind up with 20 customers, and 20 DIFFERENT systems to support. Don’t go there.

You can support 20 customers and one system. If you have to support 20 customers and 20 systems, then you will have no benefit of selling software to make money. Further, if you are not selling the system, you will all of sudden being trying to manage 20 software systems! Your time will be eaten up here trying to manage a software system out of contorl.

Further, you want your reporting system to handle all the customers, and not have to build custom reports for each customer. If you use 2 fields for one customer, then the report needs to show those two fields. If you use 7 fields, then how will your report now show 7 fields? It is very unworkable.

With a related table, then the report will automatically change (show more data) when more data is entered. Further, that one case when 11 fields are needed can thus also be handled better.

You got several comments in this thread that are quite good. The best I can offer is that you need to correctly come up with  data model that satisfies the problem at hand.

Don’t start cutting a different version of tables, or software for each customer unless you ABSOLUTELY must do this. Every new software feature will then have to be re-written for each customer, and it will cost you a bundle. If you write a way nice report for one table design, it should apply to all table designs.

Further, things like the particular customer you are working for should NOT be hard coded in this application.

Forget about worrying about tiny bit of data storage.  The smallest disk drive available in the marketplace will store more then a life time of data from any conceivable application if you are taking about text.

This is not a issue about data storage, but is an issue of data modeling, and your support costs and time you spend on this.

Machines are very cheap....humans are expensive.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Tuesday, June 10, 2003

I agree with Dan G.  Although, I think we are all saying the same thing.  Essentially, you are changing your thinking from Column Name/Column Value to Key/Value.  However, I would like some more information regarding the "system".

"For example, given ten hypothetical systems"  Could you explain more about what you mean here?  Also, I am assuming that this is for company use and you are NOT reselling this?  I used to be a Real Estate Appraiser (long, long ago), so I may be able to help out.

Either way, good luck :)

shiggins
Tuesday, June 10, 2003

Dear Marty,
                  It is difficult to understand exactly what you want.

                  I am presuming that by different system, you mean things like an electrical wiring system, the plumbing system and so on. However each person who has replied is saying something different.

                  Can you give us more details. And what knowledge do you have of database design. Have you read some books on this. Do you have a good idea of what is the relational model and what are the different levels of normalization?

                  If we can have more detailed knowledge of the particular things you are working on, and also more idea about your level of knowledge of data modelling, then we can give you some more ideas.

                   

Stephen Jones
Tuesday, June 10, 2003

Thanks to all for your replies. Dan, Deepak, and Patrik are right-on as their answers echo the third option I hinted to but decided not to elaborate on in my initial post. In answer to one of the responses, I should explain that, 'no, I am not a Newbie when it comes to understanding database normalization let alone implementing relational tables, as I have been at this for well over eight years.' Wanted to primarily sollicit feedback and thoughts from others on this particular subject matter since I work alone and as such don't often have the opportunity to exchange ideas on a regular basis with other programmers like myself. Hence, thank God for newsgroups, and forums like Joel's for people like me. Anyhow, you've all basically reinforced my initial thoughts namely, that there's no getting around the creation of additional relational tables in a case such as this one. On the other hand, while I have always tried to adhere to the rules of database normalization (pretty much common sense by now), I am also aware that there are times when the real world paradigm makes this somewhat difficult. Alas, if I had the same number of general info (or as someone else so eloquently referred to as Properties) for each system, this would have made it much easier to implement the form views but such is not the case. Thanks again to all for your feedback, and especially to people like Joel who have made this forum a reality.

Sincerely,
Marty
Potokar

Marty Potokar
Tuesday, June 10, 2003

*  Recent Topics

*  Fog Creek Home