Application with many data fields 4

[SitesMasstec]

Hello collegues!

I am building an application which needs to have a table with 400 fields.

As VFP limits the number of fields to 255, I am thing about having 2 tables:
Table A (has part I of the fields - 1 to 200)
Table B (has part II of the fields - 201 to 400)

So, when I search for a record in Table A, let's say record #123, the application will get the same record number (#123) in Table B.

Is this a good solution? Are there better ones?


Thank you,
SitesMasstec

 

[Chris Miller]

SitesMasstec,

the other aspect, of course, is how do you present this data in one form. I think your way of wanting one record with as many fields as necessary for "the whole thing" ()in this cas a reservation stems from using textbox controls, mostly, maybe even only, which you can bind to one field of one record. Well, that's not the only way controls work, you just have to widen your palette of controls to use, Listbox and Grid are two controls that each can display a list of records. Well, and a form can open as many tables as are necessary in its datasession.

Chriss

 

[Scott24x7]

I support what Chriss is advising as well.
One thing to remember, I see you have "5" of a lot of things, like 5 passengers, and each passenger can have up to 5 baggage.
With the new structure comes new responsibility. You actually are no longer bound to the "5's". That's good and bad. If the maximum number of people in a room is 5 however, you will need to make sure that you don't allow the child to have more than 5 people registered in the room at a time. That's more programmatic control than it will be database structure, but YOU will have to control that number within the table that deals with passengers and a room.
Likewise you may set baggage limits, but you don't have to. If someone brings 100 bags, that may be fine, but it also opens the opportunity for things like charging for more than 5 bags. (Increase your revenue).

I just want to get you thinking that while the child tables hold the data, ou may still have real reasons for limiting the number of records that can be associated (and allow that "no" records is also totally valid in many cases). I won't get philosophical about 1:1 vs 1:0-1 it's still 1:1 you just allow that there may not be a child record, you just have to use your program control to decide how you will manage that case.

Again, feel free to ask anything no matter what it might be. And I would advise, don't "assume" because it may not work the way you think. (VFP can be tricksy that way).


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCAP, CDCP, CDCS, CDCE, CTDC, CTIA, ATS, ATD

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[SitesMasstec]

Chris and Scott,
Just to clarify:
When I say I will create a table named RESERVA1.DBF, this table will be used for ALL first passengers, RESERVA2.DBF, will be used for ALL second (when exists) passengers, till the 5th passengers (the maximum number per cabin ship reservation).

Thank you,
SitesMasstec

 

[Chris Miller]

That's still not a good idea. But we con't force you into beter ways, if that's what you want to go for, then do so.

Just to state the situation: You're still just trying to figure out how to overcome the field number limit, but we're already discussion far away from that idea and not only talking about splitting the record you still have in your mind horizontally only. Your need for a wide table is not as mJindrova intervened could be necessary even in otherwise normalized database structures. The advice and recommendation of everyone here is good advice, I also don't assume you reject it, you just don't get the idea of data normalization, why it's good and helpful and overall leading to better working applications not only in terms of data integrity.

Chriss

 

[SitesMasstec]

Chris:
Well, I can have just one table for all passengers, but I will have to give them a status (1,2,3,4,5), besides the reservation number. Let's suppose a family will make a cruise travel (father, mother, one daughter). In this child table for passengers, besides the reservation number (unique in the master table, which coud be repeated in this child table) , I will give status numbers for each passenger, in a way the daughter does not appear as the first passenger in the Reservation Form (showed in a post above).

Thank you,
SitesMasstec

 

[Chris Miller]

SitesMasstec,

I will have to give them a status (1,2,3,4,5), besides the reservation number.

That's sensible, it's even sensible nad normal every record, no matter whether main or detail record has a unique number, that's a first rule of database design and that unique number is called a primary key.

You say it, as if it is a downside, though, a disadvantage that you now need this additional field. Well, you could also easily just let that depend on the physical record position aka recnord number and not sotre anything like that, but I'd even do so when adding the primry key value, so let that sink in, I'd even recommend more fields than your orignal idea by having both a primary key and reservatoin nuvmber and passenger number just for sake of gropuing together the data for a reservation, giving each record its uniuque identifier and be able to have a reproducable sort order of passengers.

The structuring isn't doine to have less fields, less single values anyway, it's done to structure data.

For the case of having the major passenger, I'd perhaps even make it a field bookingpassenger in the main reservation table that points to the one passenger that is responsible for the reservation and paying it. So that illustrates a case where a relationship may even go from parent to child table to point to a special record in the child data.

Chriss