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

 

[Trento777]

Hello, SitesMasstec.

One thig is "we might need up to 400 data fields..." and another is "we DO need 400 obligatory fields in a single table".

Could you further explain your specific needs?




Best Regards.

Eric. J. Muñoz
Guadalajara, Jal. Mx.

 

[Scott24x7]

My question is, why do you need a table with 400 fields?
It does not sound like you are normalizing your database. If this is one flat file 400 fields wide, I would highly recommend looking at the data you will collect, and then normalize it to 4th normal form. I suspect really quickly, you won't need a 400 field table.
It sounds unmanagable. And splitting into A and B sounds like a horrible kludge. You'll have to perform operations across both tables, and link the primary keys, which means you're also going to lose a couple of fields to link the tables together. I can thank of NO reason why you'd ever want to do this.
And if it's genuinely valid I would ask... is VFP the right tool for that?

I will turn this around slightly and ask my usual question at this point: What are you trying to achieve?


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.

 

[Rajesh Karunakaran]

Hello SitesMasstec,

I don't think, trying to relate the tables through the internal Record Number is a good idea because there are chances that at times, the same record number in the two table may not correspond to same logical record we think of. This might happen because of even unforeseen scenarios.

Why don't you keep a unique Id in your 1st table and create a ref_id in your all other other tables and then relate the tables on basis of that?
After you establish a tables relation, when you seek a record in your 1st table, the record pointer will move to the corresponding Ref_id in your other tables.
Then you can refer the fields in 2nd table, 3rd table... so on.

However, I am not sure if you would have a scenario where there wont be a record in 2nd table, 3rd table etc for a record in the 1st table.
You will have to handle this also.

Rajesh



Rajesh

 

[Mike Lewis]

Short answer: Give each of the two tables a primary key, this being an autoinc integer. then, when working with record from table A, just locate the record from table B that has the same integer key.

Not such a short answer: A single table with 400 fields indicates something seriously amiss in your database design. How on earth are you even going to keep track of all those fields, let alone work with them efficiently? Just think of all the commands and functions that take field lists as parameters (SELECT, UPDATE, REPLACE, etc.), not to mention the difficulty of viewing the fields in a Browse window or grid.

It is technically possible to set things up so that the 400 fields are divided between two tables, but I can't help thinking that is n not the way to go about it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mJindrova]

For others: Exists some tables which must not be normalize.


Hi SitesMasstec,

If will be one-to-one relation between TableA and TableB and both tables will be use same PK, then it's a good solution for native VFP tables.

If you use DB server (mySQL/MariaDB/MSSQL/Postgre SQL/MSSQL/Oracle/FireBird/etc) then if you create one or toe tables depends on capacity limits for data page size.






mJindrova

 

[Chris Miller]

To summarize the way to make a 1:1 or a 1:0-1 relation between tables: You use a primary key in any table always, no matter what situation. for relations, any relations, not just 1:n relations you use a foreign key always, too. Living by that rule you will still not treat the split virtual table as a main and a detail table, like in the usual 1:n scenario, not two tables with equal meaning that are just split out of technical reasons.

What differs in the special case of 1:1 or 1:01 is that the foreign key can be both foreign and primary key, as you explicitly only need 0 or 1 records per record of the main table. Remember, there still is one table seen as the main/major table where the existence of the theoretically longer record starts, just like an order with all order details starts with the order head data only.

It makes sense - in my book to therefore not have two autoinc fields. For several reaosns the counters maintained in the header of the two tables could go out of sync, instead you have to first generate the main record, then use its primary key for the foreign and primary key of the secondary table. Then you have a technically clean solution and can use normal means of joing by either SQL JOIN or SET RELATION.

Indeed SET RELATION also allows relating two DBFs by the mere recno, but just like two separate autoinc counters maintained in two DBFs, the reccount can easily go off by 1 or more over the lifetime of a table, no matter with what new year resolutions you intend to not break things. It always is best to relate two DBFs.

The usage of the foreign key as a primary key also ensures that the nature of the relation is kept at 1:1 or 1:0-1, you can't use a primary key as a foreign key of two records, so every main record only has one detail record, not two or more. That's a healthy good restriction that not only is a good virtue of underminig the intention, it enforces it.

There are many things that are still pointing at the recopmmendation of most to never use such a construct, as usage of such wide tables is quite resttriced in terms of possible SQL that wants to involve the two 1:1 related tables as if it really was just one. So for sake of simpler and normal usage such tables should only be done within databases that actually support them. It's pointing out the need for another database, not for this construct, if you ask me.

If you absolutely want this in VFP it's the best way to do so, I never had to use it anyway. We had data about subject of medicinical studies where we had to maintain many more properties of each subject and that wasn't stored in a wide or extended record, it was done with a normalized data structure in which properties where each having their own record. So I can't tell you about experience with such structures, I have avoided it, nevertheless it's technically straight forward.

Last not least, what does a 1:0-1 relation mean instead of a 1:1 relation. Well, the detail record in that case can exist, but does not need to exist. If you want a real 1:1 relation the insert triffer f the main table would better ensure the extension record in the detail table is hgenerated, too. It depends what rules apply to the columns of that secondary table whether it makes sense to just create a record with all default values, as the insert into the main table obviously can't have the values for the other columns of the secondary table and you have to keep them at their default. It could be sensible to make this the responsibility of the application code, not the database, to have the 1:1 relation and also insert the record extension with the generated primary key of the main record. So the database would not use any feature to check the constraint that a main record always also has a detail record and the database will treat it as a 1:0-1 case, even if the intent is t have 1:1 always. BEcause making the database stricter you don't relally have the advnatage of full records, the responsibility to fill in values into the second part of a record is in the application code anyway, you can't overcome the techjnical hurdle to pass in 400 values for all 400 columns.

To close this, let me point out that not only a wide record, bu even a large 2d set of data can be stored in a simple structure like (id, col, row, value). Notic ethat data normalisation is not about saving most bytes in storage, it is about correctly structuring your data, and that simple structure could also be used as in (record, columnnumber, value) to store as many column values as you need, all grouped by the same record column value. The need for a wide table to display it or have it in a report is not sensible, too, as the limitation would still apply to any control to display more than 255 columns, you can't. An excel sheet can, but you can feed it with cell values even from a simpler structure like (cellname, value) with as many records as there are cells you want to fill. You don't ever need anything that in a BROWSE window looks like the sheet it should end up. So make a difference of data storage vs data presentation. Data is almost never stored as presented.

Chriss

 

[Chris Miller]

To illustrate the mere technical structure of a 1:0-1 related table. it would be like this:

Create database sample
Create table main (id int autoinc, col1 c(10), col2 c(10), ... ,col200 c(10), primary key id tag id)
Create table detail (id int references main tag id, col201 c(10), col202 c(10), ... ,col400 c(10), primary key id tag id)

The "references" part of the id field definition is not reflected in the visual diagram of the database, but if you MODIFY DATABASE and drag the id from detail to main and edit the relationship you'll see that's what VFP also recognizes as one to one relationship:

Chriss

 

[SitesMasstec]

My question is, why do you need a table with 400 fields?

Scott, answering your question: I have a form with 6 pages, 1 page has about 20 fields, and 5 pages have, each one, a class (form with 72 fields). So 20 + (5 pages) x 72 = 380

For a better understanding of the problem, please see the form bellow.

My other option:
As what I have understand Chris'suggestion, I may also have 6 tables:
1 for the main data (RESERVA0.DBF) and for the 5 similar pages to have RESERVA1.DBF, RESERVA2.DBF, RESERVA3.DBF, RESERVA4.DBF and RESERVA5.DBF.

And each one will have the same primary key.

Chris, for the relationship between the two tables, is it possible to use the two tables as free ones, using the SET RELATION?


Thank you,
SitesMasstec

 

[Scott24x7]

Hi SitesMastec,
So if I understand this correctly, you can have up to 5 passengers, and each has the same data for it.
This is on "one ticket" so what you are doing is creating the same data over and over in one file to account for each of the 5 passengers.
This is the perfect candidate for normalization.
WHat you actually have is a "Passenger". There are a lot of ways to go about this, but imagine a table structure like this:

GroupNum (Identifies the group of up to 5, but in theory, you could have as many as you wish)
TicketNum (This could be unique to the passenger, I assume it is unique)
PassengerNum (This is just a unique number I'm assigning to passengers to differentiate them. It could remain unique forever for every record, or it could repeat 1, 2, 3, 4 5)

So what you have now is a single table that uses 3 pieces of date (Group+TicketNum+PassengerNum) to uniquely identify any record, but also allows you to provide grouping for the party of (any number) you wish.

You can then ditch the 5 tabs, and instead use a single tab either with a grid (might be tough with the number of fields you use), or a small navigation grid (passenger name) to easily select between passengers all on the same form without having to flip between pages, and when you click on the "passenger" name (or number or whatever you have in your navigational grid at the top), then their data (by locating that record with the 3 keys, or if you use Passenger as a unique ID you can just index on a single column and always get the right record), and then you only need the 73 fields (though looking at it, that can probably be normalized too, but I'll keep it simple).

Now you have a table with 76 fields, a way to navigate and identify unique records, and a much easier time to manage the data with a table that is in theory the same size in terms of bytes consume, but without the need for 400 fields.

Does this make sense?

There are other ways to control the selection than a grid for the passenger by name, so you can be creative with either drop-down, or other possibilities.



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.

 

[Scott24x7]

Just one more thing... looking deeper at your data, the baggage can EASILY be normalized as well, and you don't have to limit the number to 5 pieces. You may want to for other reasons, but from a data and data structure standpoint, that's not necessary. This would further reduce your number of fields required from my proposed 76 down to 51 because those fields would sit in a child table that you could reference but Passenger+TicketNum.

In short, it looks like you are treating this as a flat file database, which is one way to go, but VFP as a relational database system is strongly suited to this kind of data, and simply put it into a normalized, relational data base. I wouldn't even worry about 4th normal form as I mentioned before simply doing the two things I just mentioned will get you through this easily.

If you don't understand relational databases and how their table structures and linkages work, feel free to ask, as the approach you are taking otherwise will very quickly hit size limitation that will grind your application to a halt with just a few thousand records.


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.

 

[Chris Miller]

A form and all iut's teextboxes and other copntrols don't need to be fed by a single table, this is total nonsense. You have to get rid of that habit.

Chriss

 

[Mike Lewis]

SitesMasstec, so your rationale for having 400 fields is that you "have a form with 6 pages, 1 page has about 20 fields, and 5 pages have, each one, a class (form with 72 fields). So 20 + (5 pages) x 72 = 380".

But what you have just described is a user interface issue: it reflects how you present the information to the user. That should not constrain how you design your data structure.

Clearly, your data is crying out for some measure of normalisation. That's something you really need to accept before you can make any progress with this.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SitesMasstec]

Mike:
The form is about a cabin in a cruise ship, which can accomodate up to 5 passengers.
The cabin can be sold to 1, 2, 3, 4 or a group of 5 passengers (family members, for example), at reservations.

So, I think (maybe wrongly) that the data about a reservation has to stay in one table, which is not possible in VFP, which has the limit of 255 fields per record.

Now it seems logical to have all data passengers unified by the same primary key in 1 (main) + 5 (passengers) tables.

A term strange to me here is "normalization". What do you colleagues mean for it?



Thank you,
SitesMasstec

 

[mjcmkrsr]

Hi SitesMasstec,

You already asked a similar question in thread184-1821100. Hence you may want to reread it and apply the hints and advice this forum gave you then to this - almost - identical situation. A.o. you may want to have a closer look to my demo code how to normalize the data - the TEMPEXPL.DBF you attached to that thread

hth

MarK

 

[Chris Miller]

Sitesmasstec,

One usually good way to learn about something from top to bottom is to first just have a shallow first impression on a topic by reading one or two wikipedia topics:

https://en.wikipedia.org/wiki/Database_normalization

There are books about this topic, so this is not something that is explained or even teached in a single post or even in a single thread.

Your idea of what you need to me is driven by what you know about forms and reports, which is too basic and shallow knowledge.

For your concfrete example of a cabin record for a cruise ship is neither good as a single record per cabin reservation, nor is it necessary to have 5 passenger tables. You neither need all data about the cabin in one record, nor do you need multiple tables for the same things, that's two extremes that are both wrong thinking about how to store that data.

I could give you an idea of a design I would do, but that would also not teach you how to design a database and how you split up the data for the whole idea of a cabin reservation to as many tables as make sense.

Chriss

 

[tomk3]

Hello,

in some apps we have the similar needs.

We do
a) multiple tables "linked" with pk_master in "mastertable" and fk_master with same value in "subtables" as mentioned here

if many values may be empty or differ in length :
b) a table with fields for PKs/Fks and fields to search for + a memo field (varchar in SQLserver) holding the rest.
this has the advantage of only occupying the space you need for fields with value and more fields are not a problem. Processing for update or reading is done in our datalayer which creates a "complete object" with scatter name + several addproperty() for memocontent and builds memo on update. So we have variables for business logic and be a controlsource.

regards
tom

Btw : memo looks like the following so order or missing lines (empty value) does not matter
<EUR>value<\EUR>
<USD>value<\USD>
<XTR>value<\XTR>
..
scatter memo name omaster
addproperty(omaster,"XTR",jvals_get(omaster.memofield,"XTR","C")) && jvals_get(String/Memo , varname, format) -> omaster.xtr
addproperty(omaster,"EUR",jvals_get(omaster.memofield,"EUR","N2"))
addproperty(omaster,"USD",jvals_get(omaster.memofield,"USD","N2"))
addproperty(omaster,"GPB",jvals_get(omaster.memofield,"GPB","N2")) && omaster.gpb will be 0.00

 

[Scott24x7]

SitesMastic,
Yes the problem you have is a classic, age-old relational database issue.
No, the data does NOT need to stay in a single table, and this is why the 400 fields issue you are facing is the problem.
What a relational database does, is allows you to break "logical" pieces together. Consider that with your current approach, only 1 person books 10 times in a row. Now you have a 400 field table that has only "used" around 70 fields of the 400. That means the rest are "blank" values, but still taking up space, as well as adding complexity to the table. (Funny, sometimes the "simple" solutions are actually complexity in disguise, such as your case).

So what you might have is a master table called Reservation. That table holds the main details about the reservation. (And "ReservationNum" becomes your Primary Key across all your child tables). It also has things like, which cabin, what dates for check in and check out, all the things "unique" to that part of the reservation that don't need to be repeated in every subsequent table. Only the Primary Key (Reservation Number) is repeated and becomes the primary key. This also has all those costs associated with the reservation you have detailed, so it's your "Parent Table". All the other relations to this table are then "Child Tables".

Then you have a passenger table. For any one reservation, this will hold data for EACH passenger for that reservation. A new record each time. That will have things like PassengerID, First Name, Last Name, IDType, IDNumber, Age, birthday (you know stuff you might want to use in marketing to that person later, or while on the trip). You can keep things like PassengerID for them forever, in future reservations you'll already have data for them like Contact Details(See next table).

Then you have another table that is Contacts. This table will have the address for the passenger and will be linked by their PassengerID. You can then keep that ID for them forever, in future reservations you'll already have data for them like Address. You can also have things like phone number, email address and rather than making this complex and adding another child, just go a little overboard here and have a couple of phone number fields, (Home, mobile, work, alternate), and email fields like (Work, personal). We could normalize this table further, but for your purpose this is probably as far as you need to go.

Then you have a "Baggage" table, which ID's and tracks their baggage. The relationship then is "ReservationID+PassengerID+BaggageID" This will always allow you to uniquely find the record, and the 5 limit is no longer a limit. They can have 0 bags, 15 bags, doesn't matter, each gets its own record, and however you want to track it.

This would make managing your data infinitely easier, and your limitations on records and speed of the application will be vastly improved.

See, this is why I asked at the start "What are you trying to achieve". It's usually best on this site to pose the problem you face, and what you want to achieve, rather than posing the solution and say "How do I do it this way". Just something to think about.
We're better at solving the problem when we a) know the problem and b) then know what you're trying to achieve.

As we say in Japan, Gambate. (Battle on).


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.

 

[Scott24x7]

SitesMasstec,
(I think I finally spelled that right )
It's a bit daunting if you've not done it before. And VFP has multiple ways of dealing with this too, including keeping control of the relationships yourself. There are lots of "views" on what is the "best" way to do this, as VFP offers options some other DBs don't.
The key here is persevere, and don't be afraid to ask questions here. We'll help you get through it.
You're taking a big leap forward in your capability, and it has a steep learn curve. But it's well worth the time put in to utilize relational data bases.

Do let us know if there's anything you need in this new endovour.
Cheers



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.

 

[Chris Miller]

SitesMasstec,

you've made this one step better, but you shouldn't have a table per passenger, passengers are records of a passenger table. Of course you don't have to go with Scotts recommendation 1:1, it's also no wonder you don't get the idea, but it's really not a good way to go about things as you did. As I see it - you may correct me if I'm wrong, this cruise ship trip reservation application is a new thing you want to implement. I suggest you stick a bit on here, until we designed a good database structure together.

There aer many thoughts that become second nature. One, which Scott already gave you is that of uniqueness. As he said...

all the things "unique" (to that part of the reservation) that don't need to be repeated in every subsequent table

Another thought is that you don't create the sdame structure muultiple times, if you store something of which you have more than 1, that's in a detail table, and you don't need 5 tables for 5 passengers, you need one passenger table that holds up to 5 records per reservation. Every record will have the reservaton number ("ReservationNum" as Scott suggested) as a reference to tell that this passenger is a passneger of that reservation. There's no need for 5 tables, as you can have 5 records in one table.

Your idea is still sticking to one record per real world item you want to store in tables. That's the habit you have to break, you don't just split "the record" of a reservation into one row that has columns in many tables, when you have several of the same kind, theses are sevral records (within their own table) that contribute to "the" whole record. So you have to free yourself of that idea, you don't have one record in one table, you don't split up that record into multiple tables, you also have some of these columns that repeat in multple reecords. So you may end up with 3 tables, one main table with the 1 main record and then up to 5 records (one per passenger) in a passenger table and maybe even 24 more records in a third table, whatever.

So when you think of a reservation, say good bye to thinking about a record, think about a data set of records that make up the whole thing. You don't align the data that belongs to the whole thing by the same record number, you align 1-many records of detail tables to the one main record in the master/head table.

Chriss