cameron@kirk.nmg.bu.oz (Cameron Stevenson) (08/18/90)
Before I start with this posting, I'd like to thank all those who helped
with my last one. I must say it was enlightening to receive so many responses
to a fairly simple question, and how helpful they all were. Makes you believe
in the goodness of human nature and all that stuff. Thanks once again...
Anyway, onto this one. What I'm interested in finding out are the relative
merits of different ways of designing the table structure within a RDBMS.
The choice is simple;
1. create a table which will have many records, and simplify the access to the
system by creating a number of views which present 'like' records to the users;
or
2. create a number of tables with essentially the same structure.
Some of the questions which require answers are:
1. what effect is there on performance with these two options?
2. what about data integrity (by this I mean, what happens when more than one
person wants access to the data - will the system lock their table to
prevent other access, or will the view only be locked)?
3. what is the effect on storage requirements (if any)?
To give a quick example of the sort of application I am talking about, let's
look at a two column table for furniture:
CODE DESCRIPTION
c1 chair type 1
c2 chair type 2
c3...
t1 table type 1
t2...
d1 desk type 1
As you can see, with the codes, it would be very easy to create a number of
views for (in this example) chairs, tables, and desks.
OR
Is it better to create a number of two column tables, for chairs, tables, and
desks?
This may seem like really basic stuff, and I apologise if the issue has been
discussed at length before, but (in most cases) this sort of question cannot be
answered by reading the manuals. Thanks in advance...
Cameron Stephenson Telephone +61 75 951220
Bond University
Gold Coast Australiamfriedma@oracle.com (Michael Friedman) (08/22/90)
In article <1741@kirk.nmg.bu.oz> cameron@kirk.nmg.bu.oz (Cameron Stevenson) writes: >1. what effect is there on performance with these two options? >2. what about data integrity (by this I mean, what happens when more than one > person wants access to the data - will the system lock their table to > prevent other access, or will the view only be locked)? >3. what is the effect on storage requirements (if any)? Well, you forgot 4 and 5 4. What will the effect on usability be 5. What will the effect on maintainability be >To give a quick example of the sort of application I am talking about, let's >look at a two column table for furniture: >CODE DESCRIPTION >c1 chair type 1 >c2 chair type 2 >c3... >t1 table type 1 >t2... >d1 desk type 1 >As you can see, with the codes, it would be very easy to create a number of >views for (in this example) chairs, tables, and desks. >OR >Is it better to create a number of two column tables, for chairs, tables, and >desks? Both of these are extremely bad choices. The second is worst, but even the first is not good. I'm not going to even bother talking about choice 2 - a system that means you need to add a new table every time you add a new type of asset just isn't usable. Your choice 1 suffers from almost exactly the same problem. You need a DBA to add a new asset type. The thing to do is to sit down and think about what kind of entities you have. You're building this as if you only have 1. You have 2 - 1. Asset classes and 2. Asset types You should therefore have 2 tables ASSET_CLASS ASSET_CLASS_ID Name Description 1 Table Table 2 Mainframe Computer system suitable for over 500 users ASSET_TYPES ASSET_TYPE_ID ASSET_CLASS_ID Name Description 1 1 Chagall Table Ornat Oak, Seats 10 2 1 Kitchen Table White formica, Seats 4 3 2 NCUBE Parallel supercomputer You add asset classes using a data entry screen for that table. When you do queries, join the tables. If you want increased performance, put the asset class name in the asset type table. Then you need to make sure that if they change the name you update the table type table, but you can avoid the join. Don't bother messing with views. -- The passing of Marxism-Leninism first from China and then from the Soviet Union will mean its death as a living ideology ... . For while there may be some isolated true believers left in places like Managua, Pyongyang, or Cambridge, MA ... - Francis Fukuyama