September 18, 2017 at 8:39 pm
Hello,
I would like to understand about column arrangement in multi table relationship as mentioned below
1. Job Planning Header
2. Job Planning Details
3. JobCastingHeader
The relationship between table 1 and 2 is one-to-many and 2 and 3 is one-to-one.
Will it be correct if add the column MachineID to [JobCastingDetails].[MachineID] even though we can find the MachineID if relay on [JobCastingHeader].[JobPlanningDetailsID].?
If not could you please explain the reason.
CREATE TABLE [JobPlanningHeader](
[JobPlanningID] [int] NULL,
[JobID] [varchar](50) NULL,
[JobName] [varchar](50) NULL,
[StockItemID] [int] NULL,
[StartDate] [date] NULL,
[FinishDate] [date] NULL,
[PlannedQty] [int] NULL,
[Notes] [varchar](max) NULL,
[StatusID] [tinyint] NULL
)
CREATE TABLE [JobPlanningDetails](
[JobPlanningDetailsID] [int] NULL,
[JobPlanningID] [int] NULL,
[MachineID] [tinyint] NULL,
[MouldItemID] [smallint] NULL,
[JobDate] [date] NULL,
[ShiftID] [tinyint] NULL,
[JobQty] [smallint] NULL
)
CREATE TABLE [JobCastingHeader](
[JobCastingID] [int] NULL,
[JobPlanningDetailsID] [int] NULL,
[JobStageID] [tinyint] NULL,
[CastingDate] [date] NULL,
[CastedQty] [smallint] NULL,
[RejectQty] [smallint] NULL,
[OperatorID] [int] NULL,
[SeriallFrom] [smallint] NULL,
[SerialTo] [smallint] NULL,
[Remark] [varchar](255) NULL,
[StatusID] [tinyint] NULL
)
Thank you.
September 19, 2017 at 12:03 am
Why would you want to do that? That is only duplicating information that is already availale.
😎
September 19, 2017 at 2:55 am
Absolutely. Indeed, if the relationship between those two tables is one-to-one, why have separate tables at all? You could put everything in one table.
John
September 19, 2017 at 3:19 am
Thank you very much for your valuable suggestions.
So you mean say in any scenario if the relationship between the tables is 1:1 then we can put all attributes in one table.
Please go through below table structure. Is it fair to add CustomerID attribute in both Sales Order and Sales Invoice tables ? or can add CustomerID attribute into both tables rather than to make the query more complex.
Sales Quote
QuoteNo (PK)
CustomerID (FK)
Sales Order
OrderNo (PK)
QuoteNo (FK)
CustomerID (FK)
Sale Invoice
InvoiceNo (PK)
OrderNo (FK)
CustomerID (FK)
September 19, 2017 at 3:28 am
No, you only need it in the Sales Order table, otherwise you introduce redundancy and hence risk the integrity of your data. Don't denormalise to simplify your queries unless this is a data warehouse, or unless you've got a really good reason for doing so and you really know what you're doing.
And yes, what you say about 1:1 relationships is exactly what I meant. There may be edge cases where it's a good idea to split a table vertically, but again, only do so if you know what you're doing.
John
September 20, 2017 at 8:41 am
One reason to split a table vertically into a 1:1 relationship is when you don't need all the columns of a table to be used together. A good example might be an employee record where you want to store the employee's home address. It's unlikely you'd need the home address for a lot of things you need the employee for, so narrowing the main employee table to exclude seldom needed information can boost performance.
A second reason might be to eliminate "blank fields" in cases where a set of fields might not apply to all employees, for example if some of your employees go armed while others don't you could have a 1:1 EmployeeWeapons table that described their assigned weapon and ammunition.
A third reason for vertical partitioning might be for security reasons, giving different parts of the employee record visibility to different users. Although this effect can also be done better via views or stored procedures depending on the application's design.
September 20, 2017 at 2:51 pm
Your DDL looks fundamentally wrong. First of all, by definition, tables must have PRIMARY KEYs; what you posted can never have a key of any kind because all the columns are nullable. Basically you’ve just written out a paper form or a deck of 1950 punchcards using SQL.
What is the difference between planning identifier and a job identifier? Did you know that identifiers can never be numeric because you don’t do any math with them. Do you think that 50 characters is just a little long for a job identifier? I have the feeling you pick this number at random. You might want to get a good book on basic data modeling. For example, there is no such thing as a “status_id”; read the ISO 11179 naming rules. Both of these terms refer to what are called attribute properties and they have to be attached to an attribute. I would guess this is probably as a job status of some kind.
Why did you create your own stock item id, instead of using some industry-standard such as EAV, GTIN, or whatever?
What is the logical difference between a job planning id and a simple job id?
CREATE TABLE Job_Planning_Headers
(job_planning_id CHAR(10) NOT NULL PRIMARY KEY,
job_id CHAR(10) NOT NULL,
job_name VARCHAR(50) NOT NULL,-- very long!
stock_item_id CHAR(15)NOT NULL
REFERENCES Inventory(stock_item_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
job_start_date DATE NOT NULL,
job_finish_date DATE NOT NULL,
CHECK (job_start_date <= job_finish_date)
planned_qty INTEGER NOT NULL
CHECK (planned_qty > 0),
job_status CHAR(5) NOT NULL
CHECK (job_status IN (<list of codes>),
job_notes VARCHAR(MAX) NOT NULL);
None of these tables are related to each other; you have no references clauses! And it would not work well anyway because of all the nulls
CREATE TABLE Job_Planning_Details
(job_planning_detail_id INTEGER NOT NULL,
job_planning_id CHAR(10) NOT NULL
REFERENCES Job_Planning_Headers (job_planning_id)
ON DELETE CASCADE,
PRIMARY KEY (job_planning_detail_id, job_planning_id)
machine_id CHAR(3) NOT NULL,
mould_item_id CHAR(5) NOT NULL,
job_date DATE NOT NULL,
shift_id CHAR(2) NOT NULL,
job_qty SMALLINT NOT NULL);
CREATE TABLE Job_Casting_Headers
(job_casting_id INTEGER NOT NULL
job_planning_details_id INTEGER NOT NULL
job_stage_id CHAR(3) NOT NULL,
casting_date DATE NOT NULL,
casted_qty SMALLINT NOT NULL
CHECK(casted_qty >= 0),
reject_qty SMALLINT NOT NULL
CHECK(reject_qty >= 0),
CHECK (casted_qty >= reject_qty),
operator_id CHAR(10) NOT NULL,
start_serial_nbr CHAR(10) NOT NULL,
finish_serial_nbr CHAR(10) NOT NULL,
CHECK (start_serial_nbr <= finish_serial_nbr),
casting_notes VARCHAR(255) NOT NULL,
casting_status CHAR(3) NOT NULL);
>> Will it be correct if add the column machine_id to [JobCastingDetails].[machine_id] even though we can find the machine_id if relay on Job_Casting_Headers.job_planning_details_id?<<
The goal of every database, not just SQL is to reduce data redundancy. Why would you try to increase it? How would you keep them in sync? The question you need to ask when designing the schema is whether or not something is actually an attribute of the entity and that table. For example, one common mistake is to do things like put a book identifier in the table of authors. The book is not an attribute of an author. There is a relationship between authors and books, called authorship. If I want to do one to many, one to one or some other cardinality relationship, then I have to enforce it with referential clauses.
None of these tables are related to each other; you have no references clauses! And it would not work well anyway because of all the nulls CREATE TABLE Job_Planning_Details(job_planning_detail_id INTEGER NOT NULL, job_planning_id CHAR(10) NOT NULL REFERENCES Job_Planning_Headers (job_planning_id) ON DELETE CASCADE,PRIMARY KEY (job_planning_detail_id, job_planning_id)machine_id CHAR(3) NOT NULL,mould_item_id CHAR(5) NOT NULL,job_date DATE NOT NULL,shift_id CHAR(2) NOT NULL,job_qty SMALLINT NOT NULL);CREATE TABLE Job_Casting_Headers(job_casting_id INTEGER NOT NULLjob_planning_details_id INTEGER NOT NULLjob_stage_id CHAR(3) NOT NULL,casting_date DATE NOT NULL,casted_qty SMALLINT NOT NULL CHECK(casted_qty >= 0),reject_qty SMALLINT NOT NULL CHECK(reject_qty >= 0), CHECK (casted_qty >= reject_qty),operator_id CHAR(10) NOT NULL,start_serial_nbr CHAR(10) NOT NULL,finish_serial_nbr CHAR(10) NOT NULL, CHECK (start_serial_nbr <= finish_serial_nbr),casting_notes VARCHAR(255) NOT NULL,casting_status CHAR(3) NOT NULL);>> Will it be correct if add the column machine_id to [JobCastingDetails].[machine_id] even though we can find the machine_id if relay on Job_Casting_Headers.job_planning_details_id?<<The goal of every database, not just SQL is to reduce data redundancy. Why would you try to increase it? How would you keep them in sync? The question you need to ask when designing the schema is whether or not something is actually an attribute of the entity and that table. For example, one common mistake is to do things like put a book identifier in the table of authors. The book is not an attribute of an author. There is a relationship between authors and books, called authorship. If I want to do one to many, one to one or some other cardinality relationship, then I have to enforce it with referential clauses.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 20, 2017 at 7:35 pm
jcelko212 32090 - Wednesday, September 20, 2017 2:51 PMDid you know that identifiers can never be numeric because you don’t do any math with them.
Totally ridiculous statement there, Joe. When properly indexed and uniquified, the optimizer does a shedload of math on them to optimize all sorts of things including sorts.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2017 at 9:02 pm
jcelko212 32090 - Wednesday, September 20, 2017 2:51 PMDid you know that identifiers can never be numeric because you don’t do any math with them.Why did you create your own stock item id, instead of using some industry-standard such as EAV, GTIN, or whatever?
Advocating bad practise again, Joe? Totally unrelational EAV will make a more relational database? Identifiers not numeric because wasting vast amounts of space some other type for identifers is going to be good for performance?
Tom
September 21, 2017 at 12:58 pm
jcelko212 32090 - Wednesday, September 20, 2017 2:51 PMDid you know that identifiers can never be numeric because you don’t do any math with them.Why did you create your own stock item id, instead of using some industry-standard such as EAV, GTIN, or whatever?
Advocating bad practise again, Joe? Totally unrelational EAV will make a more relational database? Identifiers not numeric because wasting vast amounts of space some other type for identifers is going to be good for performance?
[
Please post DDL and follow ANSI/ISO standards when asking for help.
September 21, 2017 at 12:59 pm
TomThomson - Wednesday, September 20, 2017 9:02 PMjcelko212 32090 - Wednesday, September 20, 2017 2:51 PMDid you know that identifiers can never be numeric because you don’t do any math with them.Why did you create your own stock item id, instead of using some industry-standard such as EAV, GTIN, or whatever?
Advocating bad practise again, Joe? Totally unrelational EAV will make a more relational database? Identifiers not numeric because wasting vast amounts of space some other type for identifers is going to be good for performance?
[
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply