Visit Jeremy's Blog.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 08-29-2006, 07:57 AM   #1
Wim Sturkenboom
Senior Member
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Database design

I'm busy with a database. The main table contains a number of fields that each retrieve info from another table. Something like categories, symptoms etc. The user can pick one of the symptoms and one of the categories etc.




I currently store the primary keys in the fields of the main table instead of the (selected) text values.
The alternative is to store the text values.

Disadvantage of the current approach is the fact that outdated records in i.e. symptoms need to be kept to be able to retrieve the text values at later stages and that queries need joins to retrieve the text values which might have a speed impact (but maybe retrieving records with lots of text is also slow). Also the user should not be allowed to change the text in i.e. a sympton record as this influences old records. Advantage obviously is the size.

What are the guidelines to define the better approach.
Old 08-29-2006, 09:41 AM   #2
jim mcnamara
Registered: May 2002
Posts: 964

Rep: Reputation: 36
Does your db support clustered tables? Storing keys in a table has problems down the road. Use the actual text. If you need performance find a way to cluster the tables -

this means that the values in the parent table are actually physically part of the child table as well. If you have a bunch of children, then create indexes, and again use real values from columns, not a constructed key.

Are you setting constraints to prevent widows?
Old 08-29-2006, 10:21 AM   #3
Registered: May 2004
Location: Iowa USA
Distribution: CentOS
Posts: 419

Rep: Reputation: 30
Hello Wim,

Is this a simplified diagram? I ask because, judging only on what is provided here, it does not seem as though symptom, category, and area should be separate tables. Each table in a database should be an entity in the system you are modelling, and each field in the table should be an attribute of the entity. If the only fields in the other tables are the PK (which seems to be a surrogate key, at that) and the one other field, then perhaps these others are really attributes of whatever entity main represents (a disease, syndrome, disorder, or whatever).

Database design is part science, part art; but it relies on a thorough understanding of the system being modelled. Without that understanding, I can only offer suggestions. If you want to provide more details, I would be more than happy to offer further ideas.

To answer your question directly--if the other three tables should be separate tables, then you probably have the correct design. Storing symptom_txt only in symptom and not also in main, for example, avoids data redundancy. This not only saves space, as you mention, but also prevents a number of data anomalies. While the performance impact of JOINs is something to keep in mind, most RDBMSs are quite efficient at JOINs on indexed columns; a database with properly normalized tables is going to need them.

I think there is more going here than meets the eye, however, and would be more than happy to provide additional input if you were to give more details.
Old 08-29-2006, 01:21 PM   #4
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 878

Rep: Reputation: 208Reputation: 208Reputation: 208
I think your current approach (of storing the foreign keys instead of the description of the selected key) is correct. It is also classically relational. Consider an "symptom" that has to be rephrased. If you change the text of the symptom in one place, it will have an effect in all data records.

Retrival may b a little slow, but that should b OK. Most databases have a query execution plan that will show the number of selects etc. Proper indexing is the key.

You can handle outdated records (symptom masters) by marking these masters as deleted beyond a certain date (for adding new data into the main table and permitting updates).

For easy reporting, you can createa view that has the text of the selected key also. (Mind you, retrival will be run time and still a liitle slow).

Old 08-30-2006, 02:45 AM   #5
Wim Sturkenboom
Senior Member
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
@Jim mcnamara
I don't know if the database supports clustered tables. I'm running MySQL 4.0.23a at the moment although the final system will be a 4.1 flavour. I will read up on clustering.
I use MyISAM tables (without a specific reason), so no constraints.
What are widows? Records that have a foreign key set, but the record in another table with the related primary key is gone? In that case, no. (as I'm using MyISAM tables). However the application takes care of it when necessary. Although this is not 100% foolproof, maintenance mode allows the user to fix these.

Sorry that the description was not clear. I've described 4 tables in the opening post so I think that we are on the same track there.

Can you please tell me if I should index the symptom, area and category fields in the main table for performance? As they are primary keys in the 'help-tables', they are already indexed.

Although it's true that there might be a need to rephrase a symptom, I do not allow the user to do so. I desperately try to prevent that, when an user changes category_txt from 'power failure' to 'I had to go to the toilet', this change will affect the existing records that are entered with 'power failure' as it does not make sense in the records. So once e.g a symptom is defined, it can not be changed anymore. Maybe I will make it a bit more luxury and check if it's used before blocking modification. But that might cause problems in concurrent use.

A bit of info:
I'm designing an incident logging system to replace an existing one. One of the limitations in the existing one is that my employer has to call in the (external) software developer to modify the system when something trivial like a new category has to be added. This comes at a cost, can not be done immediately, etc etc.
I'm a strong believer in flexible solutions with a minimum of limitations for the user.

The system will log incidents in a broadcast network.
srvc1 -+---> tx1 ---> subscribers(a,b,c)
       +---> tx2 -+-> subscribers(d,e)
                  +---> tx3 ---> subscribers(f,g,h)
                  +---> tx4 ---> subscribers(x,y,z)

srvc2 -+---> tx2 -----> subscribers(d,e)
       +---> tx3 -----> subscribers(f,g,h)
The network topology can look like shown above (although the real one is more complex and includes satellite, terrestrial and mobile TV in a mix of old-fashioned anlog and digital).

When an incident is logged, the user selects a number of things like category, symptom, area of loss etc etc. He/she also selects one or more services (srvc) and transmitters (tx). This is the tricky part as described by the following situations:
If the user selects srvc1/tx3, this will be recorded in the database.
More complex:
If the user however selects srvc1/tx2, srvc1/tx2 and srvc1/tx3 and srvc1/tx4 will be recorded for the same incident as tx3 and tx4 receive signals from tx2.
It is also possible that due to a power failure, tx2 is down. In that situation srvc1/tx2 and srvc2/tx2 need to be recorded for the incident as well as srvc1/tx3 and srvc1/tx4 when the user selects tx2 only (without srvc).

So the database basically stores the complete network topology as well including all dependencies.

The next issue is that although srvc1/tx3 might be down as a result of srvc1/tx2 being down, it will not affect subscribers(f,g,h) as the service was not provided to them at that time (timetable). In that case srvc1/tx2 and srvc1/tx4 will be recorded for the incident but srvc1/tx3 will not be recorded.

Last edited by Wim Sturkenboom; 08-30-2006 at 02:47 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
a good book for relational database design? kpachopoulos General 3 11-20-2004 09:14 AM
More database design issues logicdisaster Programming 7 08-11-2004 05:52 PM
Database design issues logicdisaster Programming 6 08-07-2004 11:52 AM
web database design issues spyghost Programming 3 08-29-2003 06:55 PM
Database Design Question oulevon Programming 4 09-12-2001 05:38 PM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 11:58 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration