Database design

[Former Member]

Hi there,

let's assume I've these database tables:

ZROUTES: Flight routes

ZFLIGHTS: Flights

ZFLIGHTS_OPR: Flight operations

A flight route can be a rountrip which consists of many flights.

One route always has a route date. So the key fields should be:

ZROUTES

|--ROUTEID

|--RDATE

A flight within one route has a specific flight date and time. So the key must be:

ZFLIGHTS

|--ROUTEID

|--RDATE

|--FDATE

|--FTIME

For each flight I can have 0 to N operations. So the key would be:

ZFLIGHTS_OPR

|--ROUTEID

|--RDATE

|--FDATE

|--FTIME

|--OPR_NUM

Is it ok to do it this way? Or could I create a FLIGHTID for each flight in ZFLIGHTS like this:

ZFLIGHTS

|--FLIGHTID

In this case the fields ROUTEID, RDATE, FDATE and FTIME would not be part of the key any longer. When creating a new flight I would have to make shure that there is not already a flight for the same ROUTEID, RDATE, FDATE and FTIME.

But the advantage would be afterwards that I only have one foreign key entry in my table ZFLIGHTS_OPR (and every other table has a foreign key relationship with ZFLIGHTS):

ZFLIGHTS_OPR

|--FLIGHTID

|--OPR_NUM

How would you do the design? As I learned in my studies it's always better to have not redundant data in various database tables (like I have in the first solution with ROUTEID, RDATE, FDATE and FTIME). The only problem I have is to check if there's already a flight with the same values ROUTEID, RDATE, FDATE, FTIME in ZFLIGHTS, otherwise I would have two flight e.g. 0001 and 0002 for the same flight data. If ROUTEID, RDATE, FDATE, FTIME in ZFLIGHTS would be part of the key, it would be not possible to create another flight for the same data, because you cannot have duplicate keys.

So it would avoid to have wrong data in ZFLIGHTS to have a keey as mentioned in the first proposal.

But that would be the same if I have e.g. a table for staff ZSTAFF with key PERSID and none key fields NAME, GIVEN_NAME and START_DATE.

When I want to create a new person I first have to check if the same name is not already stored in the table (let's assume there's only one NAME, GIVEN_NAME and START_DATE combination).

What are your suggestions? Thanks in advance.

[matt]

Much R/3 data is based around repeat keys - so MARA has key MATNR, MARC has key MATNR, WERKS, MARD has MATNR, WERKS, LGORT (If I remember correctly ).

However, in BI, a star-schema is used, with SIDS as the identifier, and fact tables etc. (search the help for more detail).

What you propose is similar, and could lead to better performance. As you say, you need additional checks, but that shouldn't really impact performance.

You need to weigh performance and resource consumption against the complexity of the program. Space is cheap. Processing power doubles each year. The more complex a program is, however, the more it costs to develop, maintain and enhance. And you may even need a better, and therefore more expensive, programmer.

[Former Member]

Thanks for your answer.

You're right, additional checks have to be done when I create a FLIGHTID.

But otherwise I would have 6 key fields for ZFLIGHTS and 7 key fields for ZFLIGHTS_OPR.

So I prefer to use an ID in this case.

I think in terms of normalization and database design it's the better approach.

And for instance in SQL it's much more popular to use auto-increment IDs as table keys.

The bad thing in SAP is that you don't have auto-incrementation (as far as I know). You've to create a number range with an interval and have to generate the next number each time you're adding a new entry.

[matt]

I've been looking recently at persistence objects, and query services - it looks on the face of it that it could be relatively easy to develop auto-incrementation.

Regards

matt