I have a set of rows in a table that should are all logically grouped to a sequential request number.
Do I need two tables to implement this?
One table for the request number and another table that is the request number + the rows.
If there is a table with one column, how in SQL do I get a ‘new’ row such that a new identifier is created form me?
Insert nothing into table?
Most RDBMS will let you create a column defined as autonumber that you add at the beginning (usually) of the row. That will make each row have a unique number. In this scenario, only one table is involved.
Now, in a scenario such as a sales order with item lines, you would have one table with a unique order number. You may implement the order number as an autonumber column. The header table contains all the header information such as order type, order date, customer number, ship-to number, etc. All the information in this table is common to the whole order. A second table would have the item lines. The key to this table would be the order number from the header line, and the item line number. Neither is an autonumber, but the pair forms a unique combination. The nsecond table contains item line information, such as material sold, order quantity, etc.
I give these two scenarios as an example, but there are many different possible scenarios. You need to analyse your data to determine the best way to eliminate information redundancy (the first step in database normalisation). Each table in your data needs to be set-up with a key. The key can be an autonumber, as in the first example, or the combination of two or more fields (columns), which form unique combinations. A key cannot have duplicates.
You can then build views that combine all the tables in your model, joined at the common field (for example the order number). This can be done in SQL with “inner join” or left outer join" (etc.) statements. If this is as clear as a foreign language spoken backwards, I suggest investing some time researching SQL. There are many tutorials on the web, and many good books.
Hope this helps.
LD
This is what I think the relationship diagram should be… Please correct me if I’m wrong…
This would be the SQL to create the tables and the relationship between the two.
[code]-- -----------------------------------------------------
– Table DeIdentify.JobID
CREATE TABLE IF NOT EXISTS DeIdentify.JobID (
JobID INT NOT NULL AUTO_INCREMENT ,
SumissionTime DATETIME NOT NULL ,
PRIMARY KEY (JobID) )
– Table DeIdentify.JobList
CREATE TABLE IF NOT EXISTS DeIdentify.JobList (
StudyUID VARCHAR(64) NOT NULL ,
SeriesUID VARCHAR(64) NOT NULL ,
DestAET VARCHAR(64) NOT NULL ,
JobID_JobID INT NOT NULL ,
PRIMARY KEY (StudyUID, SeriesUID) ,
INDEX fk_JobList_JobID (JobID_JobID ASC) ,
CONSTRAINT fk_JobList_JobID
FOREIGN KEY (JobID_JobID )
REFERENCES DeIdentify.JobID (JobID )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
[/code]
The thing i’m confused about is the SubmissionTime column… I mean I don’t need it but I don’t know how to get a new Job ID…
If I have understood the dummies book… then I have to insert the current data/time into the JobID table and that creates a new JobID (Auto Increment)
Not really sure what the insert into the JobList would look like such that it gets the JobID from the JobID table.
The real question is what, in the second table, is dependent upon the first table? Here, I see that the primary key of your second table has nothing to do with the first table. That may be what you want, I don’t know. But this is something that you should know. As the design stands, you may have many records in the job list table for each job id record. Is this what you want? if so, then the design may work. I don’t know your business context, so it is a bit difficult to say whether the design is correct.
One thing is sure: if you wish to enforce referential integrity (it does not appear you are doing that based on your “NO ACTION” statements), then it is unusual that the join is not with the primary key of the second table. Not necessarily wrong, but unusual.
It really boils down to what business context, what business data or information, what business rule are you trying to model? If you cannot answer these simple questions, then you will have a lot of difficulty figuring out a proper design. Also try to figure out how the information is coming to you, and how is is supposed to be given back to an end user. (in other words, what queries are usually done on the data?) This is also going to help figuring out the design.
Now, to specifically answer your question, an insert into the job list would not change anything in the job id table, and vice versa. Your design does not force any relationship. That said, I often don’t bother enforcing the data integrity at table level. I usually handle that within my code, or within a stored procedure (my usual RDBMS is MS SQL Server, and I am getting up to speed on PostgreSQL for an eventual migration away from MS products. Both products let me write procedures to handle much of the database-level stuff.). My program simply feeds the necessary data for each task. This is an approach used in a major ERP (SAP). Most of the integrity work is actually done within the transactions. The tables are prepared in the transaction and then written through an update task. But the bulk of the referential integrity is handled in code. Only in some cases, referential integrity is handled at table level. So this is not an absolute must. What is a must is to understand clearly what you are trying to model or to support with the data.
Hope this helps.
LD
For PostgreSQL, the INSERT statement have the RETURNING clause where you can obtain (acts like a SELECT) the inserted value, like:
INSERT INTO masterTable (txt) VALUES (‘example for serial id, txt text’) RETURNING id;
So, you can get the ‘id’ returned and insert into the detailsTable.
How you manage a master/detail insertion depends on the DB Engine.
Yes, like Rick says. There is a way in most RDBMS. MS SQL will return an ID with IDENTITY or SCOPE IDENTITY.
While Rick is absolutely correct, I think your issues are higher level than that.
Thanks all…
I am happier just being able to have a table that has one column and is basically my applications ‘static persistant’ counter for JobID.
I was confused by the fact that if a table only had one column (an auto increment column), I didn’t know how to add a new row.
I thought it seemed pretty silly as well to have a table with useless rows.
What did an insert statement with no column data look like?
INSERT INTO JOBID RETURNING JobID as TheNewJobID;
DELETE FROM JOBID where JobID < TheNewJobID;
The INSERT didn’t need any columns.
Perhaps all that is really needed is a table with one row and one column and you get the row, increment it and put it back…
P.S. I’m using MySQL
and thanks.
Isn’t there more to a job “header” than just an ID? I would expect to see a “job” table that had a bit more info in it. That said, you can insert a NULL into the ID field and it will assign the next autoincrement.
For mysql, use GetInsertID after the insert to retrieve the ID that it assigned.