Hi,
I modeled a schema for a RDBMS but I am somewhat unsure it the modeling
was right. The problems I ran into are that working with my database
model and many famous web application frameworks (e.g. Ruby on Rails) is
very difficult because they don't seem to be able to handle my data
model the way I'd like to (Most of the automatism doesn't work and I
have to program a lot of stuff on my own so there is to sense in using
these frameworks).

However, I'd like to ask you to have a look at my schema and tell me is
this is the correct way to model it.

My domain is automobile with car dealers, car dealers' garages, gas
station, etc. Furthermore I have the following requirements:

- I have some car brands (like Ford, GM, Volkswagen, Mercedes, etc.) and
the companies dealing with cars (buyers, sellers, garages, gas stations,
etc.).

- A company can sell cars, buy cars, repair cars, recycle cars, sell gas
and do some other things which are not defined yet.

- All car brand specific actions like selling, buying, and repairing
cars can either by done for all brands or just for specific brands (one
or more). For these actions there should also be saved when it started
(e.g. Company xyz started selling Mercedes January 10, 2004).

My model so far is:

Companies (all companies dealing with cars and car related stuff):
ID |Name |Street |...|Telephone |...
-----------------------------------------------------------------
1 |Comp ABC |Broadway 1234 |...|+1-212-... |...
2 |Gas XYZ |River Road 12 |...|+1-345-... |...
....

Car brands:
ID |Name |URL |...
----------------------------------------
1 |Mercedes |http://www... |...
2 |Volkswagen |... |...
3 |GM |... |...
....

Then I have tables for what the companies do with cars:

Car buyer:
ID |CompanyID |CarBrandID |Since
-----------------------------------------------------
1 |1 |1 |2004-01-20
2 |1 |2 |2005-01-01
3 |2 |3 |1980-01-12
....

Car seller:
ID |CompanyID |CarBrandID |Since
-----------------------------------------------------
1 |1 |1 |2004-03-20
2 |2 |3 |1980-02-13
....

Gas station (in fact Gas Brand is a different table referenced from here):
ID |CompanyID |Gas Brand |Since
-----------------------------------------------------
1 |3 |Mobil |1980-05-10
....


Does this schema make sense and does anyone have an idea if I can
improve it?

Thanks,
Hilmar

On Jan 30, 9:59 am, Hilmar <newsgr... (AT) silveraxe (DOT) de> wrote:
That's not a shema, it is a list of data.

Obviously you are already working on the physical model (as opposed to
the logical model which should be your first step). So show your
physical model. IOW, post your DDL for the model: CREATE TABLE
statements, constraint statements, etc. We don't have time to reverse
engineer the model from your data.

help us help you. We aren't mind reader you know.
Ed

Hi Ed,

Sorry for just giving an overview over the tables with data. I thought
it could be better to show it that way. Here are my create table
statements (postgresql) including constraints. There are also some
triggers and indexes but they don't add any value to the model I'd like
to show.

CREATE TABLE companies
(
id serial NOT NULL,
mother_id integer,
name character varying(254),
adr_street character varying(254),
... (some more fields)

CONSTRAINT "PK_company_id" PRIMARY KEY (id),
CONSTRAINT "FK_company_companymother" FOREIGN KEY (mother_id)
REFERENCES compoanies (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE SET NULL
)

CREATE TABLE carbrands
(
id serial NOT NULL,
name character varying(50),
name_full character varying(254),
... (some more fields)

CONSTRAINT "PK_carbrands_id" PRIMARY KEY (id),
)

CREATE TABLE gasbrands
(
id serial NOT NULL,
name character varying(50),
name_full character varying(254),
... (some more fields)

CONSTRAINT "PK_gasbrands_id" PRIMARY KEY (id),
)

CREATE TABLE car_buyer
(
id serial NOT NULL,
company_id integer NOT NULL,
carbrand_id integer NOT NULL,
since timestamp with time zone,

CONSTRAINT "PK_car_buyer_id" PRIMARY KEY (id),
CONSTRAINT "FK_car_buyer_carbrand" FOREIGN KEY (carbrand_id)
REFERENCES carbrands (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "FK_car_buyer_company" FOREIGN KEY (company_id)
REFERENCES companies (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)

The other tables like car_seller, car_repaigarage, ... look like car_buyer.

CREATE TABLE gasstations
(
id serial NOT NULL,
company_id integer NOT NULL,
gasbrand_id integer NOT NULL,
since timestamp with time zone,

CONSTRAINT "PK_gasstation_id" PRIMARY KEY (id),
CONSTRAINT "FK_gasstation_gasbrand" FOREIGN KEY (gasbrand_id)
REFERENCES gasbrands (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "FK_gasstation_company" FOREIGN KEY (company_id)
REFERENCES companies (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)


I hope that helps you to help me.

Thank you very much,
Hilmar

This is NOT a relational design at all AND you have done no research.

1) Automobiles are identified by a VIN, which you would have found
easily. It is required by law
2) An RDBMS uses keys and not serial numbers!!
3) Columns are NOT fields ; your use of the wrong terms is one reason
you cannot get the design right.
4) Have you ever seen a street address that is 254 characters long?
NO! The USPS standard is CHAR(35). Most of the other columns also show
no research or even a rational guess as to size, defaults or data
types.
5) There is no such thing as a generic "name" or an "id" -- they have
to be the name of something in particular. That data element names
stays the same everywhere in the schema.
6) Everything is NULL-able in your world, so you cannot ever have a
relational key, DRI, etc.

CREATE TABLE Companies
(duns CHAR(9) NOT NULL PRIMARY KEY, -- industry standard code
company_name VARCHAR(35) NOT NULL,
company_street VARCHAR(35) NOT NULL,
... );


ON UPDATE RESTRICT ON DELETE SET NULL <<

The DUNS number will give this to you, but if you must model hierarchy
use a nested sets model, orinclude a column for the parent company

Did you bother to look up the make and model codes that are industry
standards?? They are also in the VIN.

A car buyer purchases a particular vehicle :

CREATE TABLE Car_Buyers
(duns CHAR(9) NOT NULL
REFERENCES Companies (duns)
ON UPDATE CASCADE
ON DELETE CASCADE,
vin CHAR(17) NOT NULL
REFERENCES Automobiles(vin)
ON UPDATE CASCADE
ON DELETE CASCADE,
purchase_date TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (duns, vin)); -- this is the relational key

then they need to be changed also.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote:

[snip]

TWIAVBP.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

On Jan 31, 2:49 pm, Hilmar <newsgr... (AT) silveraxe (DOT) de> wrote:


Some how my reply got lost. Since then Joe Chelko has pointed out
several places for improvement. I'll just point out one he did not
mention. In your car buyer, the constraints do not work. With this
design, a buyer can purchase a AUDI PASSAT or a VOLKSWAGEN FOCUS. And
given many other tables in your design "look like car_buyer", I
expect many other similar problems.

I would suggest you go back to the logical design level. Redo your ERD
(Entity Relationship Diagram). You didn't do one, did you? We could
tell.

So does that help?
Ed

Hi Celko,
first I'd like to say that the schema should fit to German and European
requirements so that could lead to misunderstanding.

I understand some of your following points but not why there is not
relational design at all in my design.


I have no cars in my database, only the different brands (GM, Ford,
Volkswagen, etc.) so I cannot put the VINs in my design as far as I see.
Furthermore I also cannot take just the manufacturer id of the VIN
because I can have brands in my database which does not have manufactu

What do you mean with keys instead of serial numbers? The type "serial"
in PostgreSQL is an integer which has an attached sequence. As I don't
want to use the names of the car brands as keys I decided to create the
generic ids.

Sorry, I took the wrong translation in this case.

As I said above 35 characters are not enough for street names in Germany
so I decided not to cut it down in the database.

OK, I see that there are some colunms which shouldn't be nullable. I
corrected them.

What is wrong with my key definiton? I think I don't get the point here?

I think we have a misunderstanding of the table Car_Buyers. In my model
the car_buyer is a company which buys one or more specific car brands. I
don't have automobiles in my model but only companies and the car brands.

Thank you very much for your help,
Hilmar

There is a standard abbreviation code for make and model. The
quickest place to find it is from a police station -- the codes are
used internationally for stolen cars.

You have cars that appear by magic? Ther are codes for custom made
vehicles.

There is no such thing as a generic identifier in RDBMS. An
identifier is always particular by definition. What you have an
exposeed physical locator derived from the internal state of the
hardware. It is not part of any relational data model at all. You
are mimicking a record number in a 1950's file system.

No, translation is not the problem. The problem is concepts. Like
most new ideas, the hard part of understanding what the relational
model is comes in un-learning what you know about file systems. As
Artemus Ward (William Graham Sumner, 1840-1910) put it, "It ain't so
much the things we don't know that get us into trouble. It's the
things we know that just ain't so."

If you already have a background in data processing with traditional
file systems, the first things to un-learn are:

(0) Databases are not file sets.
(1) Tables are not files.
(2) Rows are not records.
(3) Columns are not fields.

Modern data processing began with punch cards. The influence of the
punch card lingered on long after the invention of magnetic tapes and
disk for data storage. This is why early video display terminals
were 80 columns across. Even today, files which were migrated from
cards to magnetic tape files or disk storage still use 80 column
records.

But the influence was not just on the physical side of data
processing. The methods for handling data from the prior media were
imitated in the new media.

Data processing first consisted of sorting and merging decks of punch
cards (later, sequential magnetic tape files) in a series of distinct
steps. The result of each step feed into the next step in the
process. This leads to temp table and other tricks to mimic that kind
of processing.

Relational databases do not work that way. Each user connects to the
entire database all at once, not to one file at time in a sequence of
steps. The users might not all have the same database access rights
once they are connected, however. Magnetic tapes could not be shared
among users at the same time, but shared data is the point of a
database.

Tables versus Files

A file is closely related to its physical storage media. A table may
or may not be a physical file. DB2 from IBM uses one file per table,
while Sybase puts several entire databases inside one file. A table
is a <i>set<i> of rows of the same kind of thing. A set has no
ordering and it makes no sense to ask for the first or last row.

A deck of punch cards is sequential, and so are magnetic tape files.
Therefore, a <i>physical<i> file of ordered sequential records also
became the <i>mental<i> model for data processing and it is still hard
to shake. Anytime you look at data, it is in some physical ordering.

The various access methods for disk storage system came later, but
even these access methods could not shake the mental model.

Another conceptual difference is that a file is usually data that
deals with a whole business process. A file has to have enough data
in itself to support applications for that business process. Files
tend to be "mixed" data which can be described by the name of the
business process, such as "The Payroll file" or something like that.

Tables can be either entities or relationships within a business
process. This means that the data which was held in one file is often
put into several tables. Tables tend to be "pure" data which can be
described by single words. The payroll would now have separate tables
for timecards, employees, projects and so forth.

Tables as Entities

An entity is physical or conceptual "thing" which has meaning be
itself. A person, a sale or a product would be an example. In a
relational database, an entity is defined by its attributes, which are
shown as values in columns in rows in a table.

To remind users that tables are sets of entities, I like to use
collective or plural nouns that describe the function of the entities
within the system for the names of tables. Thus "Employee" is a bad
name because it is singular; "Employees" is a better name because it
is plural; "Personnel" is best because it is collective and does not
summon up a mental picture of individual persons.

If you have tables with exactly the same structure, then they are sets
of the same kind of elements. But you should have only one set for
each kind of data element! Files, on the other hand, were PHYSICALLY
separate units of storage which could be alike -- each tape or disk
file represents a step in the PROCEDURE , such as moving from raw
data, to edited data, and finally to archived data. In SQL, this
should be a status flag in a table.

Tables as Relationships

A relationship is shown in a table by columns which reference one or
more entity tables. Without the entities, the relationship has no
meaning, but the relationship can have attributes of its own. For
example, a show business contract might have an agent, an employer
and a talent. The method of payment is an attribute of the contract
itself, and not of any of the three parties.

Rows versus Records

Rows are not records. A record is defined in the application program
which reads it; a row is defined in the database schema and not by a
program at all. The name of the field in the READ or INPUT statements
of the application; a row is named in the database schema. Likewise,
the PHYSICAL order of the field names in the READ statement is vital
(READ a,b,c is not the same as READ c, a, b; but SELECT a,b,c is the
same data as SELECT c, a, b.

All empty files look alike; they are a directory entry in the
operating system with a name and a length of zero bytes of storage.
Empty tables still have columns, constraints, security privileges and
other structures, even tho they have no rows.

This is in keeping with the set theoretical model, in which the empty
set is a perfectly good set. The difference between SQL's set model
and standard mathematical set theory is that set theory has only one
empty set, but in SQL each table has a different structure, so they
cannot be used in places where non-empty versions of themselves could
not be used.

Another characteristic of rows in a table is that they are all alike
in structure and they are all the "same kind of thing" in the model.
In a file system, records can vary in size, datatypes and structure by
having flags in the data stream that tell the program reading the data
how to interpret it. The most common examples are Pascal's variant
record, C's struct syntax and Cobol's OCCURS clause.

The OCCURS keyword in Cobol and the Variant records in Pascal have a
number which tells the program how many time a record structure is to
be repeated in the current record.

Unions in 'C' are not variant records, but variant mappings for the
same physical memory. For example:

union x {int ival; char j[4];} myStuff;

defines myStuff to be either an integer (which are 4 bytes on most
modern C compilers, but this code is non-portable) or an array of 4
bytes, depending on whether you say myStuff.ival or myStuff.j[0];

But even more than that, files often contained records which were
summaries of subsets of the other records -- so called control break
reports. There is no requirement that the records in a file be
related in any way -- they are literally a stream of binary data whose
meaning is assigned by the program reading them.

Columns versus Fields

A field within a record is defined by the application program that
reads it. A column in a row in a table is defined by the database
schema. The datatypes in a column are always scalar.

The order of the application program variables in the READ or INPUT
statements is important because the values are read into the program
variables in that order. In SQL, columns are referenced only by their
names. Yes, there are shorthands like the SELECT * clause and INSERT
INTO <table name> statements which expand into a list of column names
in the physical order in which the column names appear within their
table declaration, but these are shorthands which resolve to named
lists.

The use of NULLs in SQL is also unique to the language. Fields do not
support a missing data marker as part of the field, record or file
itself. Nor do fields have constraints which can be added to them in
the record, like the DEFAULT and CHECK() clauses in SQL.

Relationships among tables within a database

Files are pretty passive creatures and will take whatever an
application program throws at them without much objection. Files are
also independent of each other simply because they are connected to
one application program at a time and therefore have no idea what
other files looks like.

A database actively seeks to maintain the correctness of all its
data. The methods used are triggers, constraints and declarative
referential integrity.

Declarative referential integrity (DRI) says, in effect, that data in
one table has a particular relationship with data in a second
(possibly the same) table. It is also possible to have the database
change itself via referential actions associated with the DRI.

For example, a business rule might be that we do not sell products
which are not in inventory. This rule would be enforce by a
REFERENCES clause on the Orders table which references the Inventory
table and a referential action of ON DELETE CASCADE

Triggers are a more general way of doing much the same thing as DRI.
A trigger is a block of procedural code which is executed before,
after or instead of an INSERT INTO or UPDATE statement. You can do
anything with a trigger that you can do with DRI and more.

However, there are problems with TRIGGERs. While there is a standard
syntax for them in the SQL-92 standard, most vendors have not
implemented it. What they have is very proprietary syntax instead.
Secondly, a trigger cannot pass information to the optimizer like
DRI. In the example in this section, I know that for every product
number in the Orders table, I have that same product number in the
Inventory table. The optimizer can use that information in setting up
EXISTS() predicates and JOINs in the queries. There is no reasonable
way to parse procedural trigger code to determine this relationship.

The CREATE ASSERTION statement in SQL-92 will allow the database to
enforce conditions on the entire database as a whole. An ASSERTION is
not like a CHECK() clause, but the difference is subtle. A CHECK()
clause is executed when there are rows in the table to which it is
attached. If the table is empty then all CHECK() clauses are
effectively TRUE. Thus, if we wanted to be sure that the Inventory
table is never empty, and we wrote:

CREATE TABLE Inventory
( ...
CONSTRAINT inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0), ... );

it would not work. However, we could write:

CREATE ASSERTION Inventory_not_empty
CHECK ((SELECT COUNT(*) FROM Inventory) > 0);

and we would get the desired results. The assertion is checked at the
schema level and not at the table level.

But you never bothered to research what the German Post Office uses
for a standard, did you? You simply pulled a magic number out of the
air.

Ed Prochak wrote:
As far as I understand the relation does exactly what the OP wanted.
Basically he is creating an M:N relationship between Companies and Car
Brands to contain the information on which companies buy which brands,
eg. a company may be a car dealer that buys VW and Audi for reselling.
The models have no place here, so I guess either the company must buy
all models of a brand (or none) or it is not necessary to keep
information on which models a company buys.
Except for the fact that there is no need for the extra id attribute,
this perfectly ok.

Kind regards,
Axel Hallez