Relational Databases / Domain Tables question

Database Scheme

[Gruessle]

Hello mysql gurus,

I been reading and reading about Relational Databases, Object Data Bases, Object-Relational, Primary and Foreign Keys, Referential Integrity, Database Scheme, Domain Tables etc. and I know a whole more about it then I did yesterday but making a decision when it comes to my situations – well I am over my head here and was wondering if someone could just point me in the right direction.

Here is what I got:
1)Client
2)Companies
3)Broker
4)Products

The problems is with the Products.
Each Company has several products. This database shell help the broker find the right product from a set of products.

Each product has several define limits. Some of the limits are just int entries like a number between 1 and 1000 and others are Domains.

Domain samples are:
States / each product may be available in all or only some states
Status: Single, Married, Divorced / Some products are available only to one of those and others to all or even to some of those.

I have about 6 of those domains. Those are Domains right?

Anyhow should I make a single table for products or should I create domain tables like
Table:States
Fields: state_id, state_name, prod_id

Table Status
Fields: status_id, status_name, prod_id

[badone]

If you create one large table it will be less flexible and more difficult to work with.

Proceed with your "domain" (unfamiliar with the term) tables, you're on the right track.

Cheers,
BAD

[Gruessle]

It's surely is easy but only if you know how and here is something i don't know ;)

Now I did create several tables, lets take my regions table as an example.

Field1 ¦ Field2 ¦ Field3 ¦
-------------------------------
reg_id ¦ US_States ¦ progr_id ¦
-------------------------------

I am going to insert data the first time so i would say

INSERT INTO region (US_States,progr_id)
VALUES ('$States','$progr_id')

But I don't know what the progr_id is going to be before I execute this insert because progr_id is an auto_increment from another table which is being created now with the same form.

Do I have to first insert the progr_id table then read it to find out what number it received via auto_increment and then execute this INSERT or is there a trick to do this?

[badone]

This will be database dependant.

Unfortunately I'm familiar with PostgeSQL where the syntax to get the next sequence ID is;

SELECT nextval ('"reg_id_seq"')

You would then include reg_id when you do the insert thus;

INSERT INTO region (reg_id, US_States,progr_id)
VALUES ('$reg_id', '$States','$progr_id');

MySQL will have something similar/identical to nextval()

HTH,
BAD

[dmmh]

Do I have to first insert the progr_id table then read it to find out what number it received via auto_increment and then execute this INSERT or is there a trick to do this?

yes

get the last insert id with mysql_insert_id()

[nl3.php.net...]

[Gruessle]

This is what i did after the first insert
HTTP_POST_VARS['progr_id'] = mysql_insert_id();

And this works for now but what happens if two or more people post almost at the same time?

[dmmh]

what you showed in your above post is odd. You are overwriting a $_POST variable? Why? You're not providing enough information to give an conclusive answer :)

[Gruessle]

Well it is because I switched to macromedia. And if I edit the code macromedia created then macromedia gets confused so here is what my code looks like:

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO programs (Progr_Name, Status) VALUES (%s, %s)",
GetSQLValueString($HTTP_POST_VARS['Program'], "text"),
GetSQLValueString($HTTP_POST_VARS['Status'], "int"));

mysql_select_db($database_MTG, $MTG);
$Result1 = mysql_query($insertSQL, $MTG) or die(mysql_error());
}
// Data from Multi Select Form below
$HTTP_POST_VARS['progr_id'] = mysql_insert_id();

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO region (reg_id, US_States, progr_id) VALUES (%s, %s, %s)",
GetSQLValueString($HTTP_POST_VARS['progr_id'], "int"),
GetSQLValueString($HTTP_POST_VARS['GState'], "text"),
GetSQLValueString($HTTP_POST_VARS['progr_id'], "int"));

mysql_select_db($database_MTG, $MTG);
$Result1 = mysql_query($insertSQL, $MTG) or die(mysql_error());
}