473,854 Members | 1,760 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

good table design...

Hello,

I am designing a table of vehicle types, nothing special, just a list of
unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor
etc etc

For the table design I am proposing a single column table with a field name
called vehicle_type and this will contain the vehicle type.

Sot it will be

vehicle_type
car
bike
tractor
plane
truck
van
blah
blah
blah

Is this ok? Or is there a better way to do it?

Thanks,

td.

Jul 20 '05
36 4664
> A simple unique index or constraint on the description would prevent the
scenario you just described.
That's what I was suggesting. You didn't mention the importance of declaring
the natural key so I was just making the point for the benefit of the OP.
"Never"? I also disagree with that statement. There are perfectly
acceptable uses for that design.


As part of an ETL process it may be acceptable in a staging table to use
only an artificial key. Not in a relational schema. Without a natural key by
implication you have redundant data and by definition no meaningful way to
define the entity you are modelling.

The cases I've come across that perhaps justify an exception to this are
when you have an automatic, event-driven process which logs to a table
without human intervention. It may not be feasible as part of a real-time
logging process to ensure that a natural key is enforced. The sequence of
events is the data you are attempting to capture but the database system may
not support a date/timestamp of sufficient precision to guarantee that each
event has a unique time. This is really a problem of application design
rather than relational database design. You still generate redundant data
but in a log that isn't always a big problem.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #21
Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:
You realize an autoincremented integer, at least in MS SQL Server is
terrible for this. You can't guarantee that you won't have gaps and you
can't even guarantee that the numbers will remain the same. DBCC
CHECKIDENT can reset things on you, copying them to another DB may
completely change the numbers, etc.


Gaps are a non-issue in this case.

That said, you are terribly lazy if you need to have auto-incremented
ids for a simple lookup table. Better assigned the ids manually.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #22
-P-
"David Portas" <RE************ *************** *@acm.org> wrote in message news:GL******** ************@gi ganews.com...
A simple unique index or constraint on the description would prevent the scenario you just described.


That's what I was suggesting. You didn't mention the importance of declaring the natural key so I was just making the
point for the benefit of the OP.
"Never"? I also disagree with that statement. There are perfectly acceptable uses for that design.


As part of an ETL process it may be acceptable in a staging table to use only an artificial key. Not in a relational
schema. Without a natural key by implication you have redundant data and by definition no meaningful way to define the
entity you are modelling.

The cases I've come across that perhaps justify an exception to this are when you have an automatic, event-driven
process which logs to a table without human intervention. It may not be feasible as part of a real-time logging
process to ensure that a natural key is enforced. The sequence of events is the data you are attempting to capture but
the database system may not support a date/timestamp of sufficient precision to guarantee that each event has a unique
time. This is really a problem of application design rather than relational database design. You still generate
redundant data but in a log that isn't always a big problem.

We still disagree. I would never use autoincrement in situations where a natural key was evident and available, but I
still contend that there is a place for autoincrement in the relational model. What about entities that have no
identifiable "natural" key? We have several in our model, and had to invent an identification scheme for them.

--
Paul Horan
Jul 20 '05 #23
> What about entities that have no identifiable "natural" key? We have
several in our model, and had to invent an identification scheme for them.


There are two separate issues and maybe neither of us have spelt them out
well enough.

1) When you model an entity in a relational database it must always have a
natural key by definition (= "a subset of the attributes that uniquely
identifies a row"), otherwise you have redundancy and no integrity. For
example that key could be the vehicle description in Toedipper's case. If
you have a table without a natural key then someone has failed to identify
the entity properly in the logical model. However the natural key may not
necessarily be convenient for use as a foreign key (because of storage or
performance considerations for example).

2) In cases where the data changes infrequently it may additionally be
desirable to create your own user-assigned surrogate key, such as "C" for
"Car" for example. In other cases, a system-assigned surrogate (such as
IDENTITY in SQL Server) is often used. Surrogate keys are not a substitute
for a natural key, which should still be declared as a key of the table.
Unfortunately system-assigned "row-identifier" surrogates are too often used
carelessly by those who fail to design a proper logical model and think they
don't need real keys. System-assigned surrogates are not part of the logical
data model at all - they are part of the physical implementation.

There is of course room for a great deal of debate about the wisdom or
otherwise of using system-assigned surrogate keys at all. There are
reasonable arguments on both sides and I don't want to go over that debate
again here. What isn't usually disputed is that a surrogate key should never
be the ONLY key of a table. As soon as you compromise that principle your
data model is lost and you have big logical problems with data integrity and
often insurmountable practical problems when it comes to getting meaningful
information from the data.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #24
David Portas (RE************ *************** *@acm.org) writes:
There is of course room for a great deal of debate about the wisdom or
otherwise of using system-assigned surrogate keys at all. There are
reasonable arguments on both sides and I don't want to go over that
debate again here. What isn't usually disputed is that a surrogate key
should never be the ONLY key of a table. As soon as you compromise that
principle your data model is lost and you have big logical problems with
data integrity and often insurmountable practical problems when it comes
to getting meaningful information from the data.


I would say that there certainly are cases where some sort of system-
generated key is the only possible key. Take for instance a table with
account transactions. You can fairly well describe a transaction by using
account number, date and time of day. But there may be two transactions
for the same account in the same millisecond, so those three alone cannot
make a key. You can then try to find some constraint that distinguishes
two transactions that happen at the same time. (Typically they would be
generated by some batch process.) But you would then only trying to intro-
duce a constraint that has no relation to business rules, and one day
you will get a failure for a perfectly valid transaction, because it
did not fit into the squared model. That's when a surrogate key is the
way to go.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #25
That depends on how you define the transaction entity. For example:

Account Timestamp Amount
------- ----------------------- -------
1234 2004-11-01 09:10:01.104 512.99
1234 2004-11-01 09:10:01.104 512.00
might be adequately represented as:

Account Timestamp Amount
------- ----------------------- -------
1234 2004-11-01 09:10:01.104 1024.99
Or even:

Account Timestamp Amount Trancount
------- ----------------------- ------- ---------
1234 2004-11-01 09:10:01.104 1024.99 2
In reality, at least in the financial systems I have worked with,
transactions contain more information that this. They are identified as part
of a batch by a unique batch number or journal number which is assigned at
the time the batch is generated. The batch number is itself a surrogate for
an entity composed of something like (orignating_ent ity, location,
datetime).

--
David Portas
SQL Server MVP
--
Jul 20 '05 #26
David Portas (RE************ *************** *@acm.org) writes:
That depends on how you define the transaction entity. For example:

Account Timestamp Amount
------- ----------------------- -------
1234 2004-11-01 09:10:01.104 512.99
1234 2004-11-01 09:10:01.104 512.00
might be adequately represented as:

Account Timestamp Amount
------- ----------------------- -------
1234 2004-11-01 09:10:01.104 1024.99
That is very likely to be completely unacceptable.
In reality, at least in the financial systems I have worked with,
transactions contain more information that this.
Yes, there is very likely to be more information: transaction type,
transaction text etc. And some of these may be different, which is
why you cannot collapse two transactions into one. But the problem is
that you would have include about every column in the table, to not
put up a roadblock for a pair of transactions that are valid for the
real-world business.
They are identified as part of a batch by a unique batch number or
journal number which is assigned at the time the batch is generated. The
batch number is itself a surrogate for an entity composed of something
like (orignating_ent ity, location, datetime).


Yes, there may be such a thing. But not all transactions may be booked
in this way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #27
> you would have include about every column in the table, to not
put up a roadblock for a pair of transactions that are valid for the
real-world business.


Q.E.D.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #28
-P-
I've seen models that take the use of autoincrement to the extreme, where EVERY table has a sequence column as the PK,
and that's also wrong. I'm certainly not advocating that - but I do take issue with your assertion that there is NO
PLACE for autoincrement in a relational database.

For example, our Order table for storing commercial airtime orders from Ad agencies to TV stations... There are about
30 columns that help to describe the parameters of the order, including the Advertiser_ID, an Agency_ID (both fk
references to the Name_Address table), some dates, various and sundry codes, revision counters, and some descriptive
text... You're suggesting that we find the 7 or 8 columns that combine to uniquely identify an Order (including the
aforementioned descriptive text column) and call those the primary key? And then, we get to replicate all that data on
every row of every table that references Order (roughly 20 additional tables, up to 5 levels deep in places).

This poor design is solved by creating the Order_ID column as an autoincrementin g number, and using that as the primary
key. The references to Order then use Order_ID as the foreign key. No muss, no fuss. Significantly less duplication
of data, greatly increased performance for JOIN processing, and MUCH easier to work with from a development standpoint.

-Paul-
"David Portas" <RE************ *************** *@acm.org> wrote in message news:m5******** ************@gi ganews.com...
you would have include about every column in the table, to not
put up a roadblock for a pair of transactions that are valid for the
real-world business.


Q.E.D.

--
David Portas
SQL Server MVP
--

Jul 20 '05 #29
> I do take issue with your assertion that there is NO PLACE for
autoincrement in a relational database.
I didn't say that. Some people take the view that you should never use
"autoincrementi ng" keys but I'm not one of them.

You're suggesting that we find the 7 or 8 columns that
combine to uniquely identify an Order
Identifying the keys is an essential part of the process of designing the
logical model anyway.

(including the aforementioned descriptive text column) and call
those the primary key? And then, we get to replicate all that data
on every row of every table that references Order (roughly 20
additional tables, up to 5 levels deep in places).
No. You missed my point. Yes, create a compact surrogate key if you need to
and use that in other tables as the foreign key. But it is still essential
ALSO to declare the *natural* key columns. You don't have to duplicate the
natural key in any referencing tables but you do have to *declare* the key
in the parent table. From before: "An autoincrementin g surrogate key should
never be the only key of a table." The key word is "only". That was the
assertion that you originally disagreed with but none of what you have said
contradicts that statement so maybe we agree after all. :-)

This poor design is solved by creating the Order_ID column as an
autoincrementin g number, and using that as the primary key. The
references to Order then use Order_ID as the foreign key. No muss, no
fuss. Significantly less duplication of data, greatly increased
performance for JOIN processing, and MUCH easier to work with from a
development standpoint.


Agreed. System-generated "autoincrementi ng" keys aren't the only option for
a surrogate key but they do have their uses. I don't have anything against
surrogate keys - only against poorly designed tables without natural keys,
mainly because in my career I've spent a lot of time identifying and fixing
problems caused by other people's weak schema designs.
--
David Portas
SQL Server MVP
--
Jul 20 '05 #30

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

24
3634
by: matty | last post by:
Go away for a few days and you miss it all... A few opinions... Programming is a craft more than an art (software engineering, not black magic) and as such, is about writing code that works, first and foremost. If it works well, even better. The same goes for ease of maintenance, memory footprint, speed, etc, etc. Most of the time, people are writing code for a use in the *real world*, and not just as an academic exercise. Look at...
52
6458
by: Tony Marston | last post by:
Several months ago I started a thread with the title "What is/is not considered to be good OO programming" which started a long and interesting discussion. I have condensed the arguments into a single article which can be viewed at http://www.tonymarston.net/php-mysql/good-bad-oop.html I fully expect this to be the start of another flame war, so sharpen your knives and get stuck in!
1
4211
by: David Thompson | last post by:
Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like.... Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideas....thanks...
3
2033
by: cassandra.flowers | last post by:
I'm designing a database because I have to do it for the preperation work for my A-Level ICT exam. The database is for a building company. It has to store information on building projects (tblProject) and employees (tblPersonnel) then link them together.
15
2744
by: Hi5 | last post by:
Hi, I am designing a database for a client in which It has a client table including the followings: 1-Table Client 2-Table lookupcategory 3-Table Ctegory
7
1853
by: farseer | last post by:
Here is the scenario: I have an interface which defines get methods for data that will make up a row in a table. However, the source of this data may, over time, switch/change (The company may choose to change data providers). Therefore i thought to myself, a type of Adapter Pattern is best here and so i proceeded with that. here's an example of what i did (note this implementation differs from the text book one due to the way data...
4
3941
by: Nathan Sokalski | last post by:
When editing an ASP Table, Visual Studio does not allow me to edit it in Design View. This makes it harder to add elements, because I must add every element either by using Design View to create the element outside of the table and then using cut & paste in HTML View to move it to the desired location, or by manually typing the code in using HTML View. The first technique sometimes does not automatically update the list of elements...
7
4870
by: david | last post by:
I have asked this question before. But it does not work for me. Given radion buttons in the web form design page. What I did is described as follows. A panel control is dragged into the design form, and a table control is dragged into the panel from tooolbox. Add cells for the table through the properties. Now I cut a radio button, and click the table (note I can not select a cell), then right click Paste. The button is not in expected...
3
1354
by: Chris | last post by:
Hi again, I need another good practise advice. Everybody (100 persons) in our compagny manages one or more projects. Each projects needs two tables: table 'project' (general information), table 'projectdetails' (details of projects) and one or more result tables (one per project) like 'projectresult1', 'projectresult2' etc ... One project needs more or less 300 records with 6 fields (with size: nvarchar(200) average)). Nothing...
0
9903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10767
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10375
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7927
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5754
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5952
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.