Anonymous 776
First time reader. First time poster to TopAnswers.
Thank you for reading this question, despite my poor title. I could not think of a succinct and accurate title.
I am building a small sqlite database for a personal project. I am using Python and SqlAlchemy. I'm doing this mostly for the challenge and for the self education.
I have a table "components". One of the attributes of this table is "type". "type" is restricted to only three values, which I am going to enforce through SqlAlchemy's enum.
Each of the three "types" have "rates" associated with them.
Type "A" is measured in "parts / second"
Types "B" and "C" are both measured by the amount of time it takes to make one part (seconds / part).
I am not sure of the most appropriate way of handling the difference in rate measurements.
Option 1:
Should I simply invert A to be like B & C (or vice versa). The reason I am a little leery about this option is rounding errors.
Option 2:
If I decide to not simply change the rates measure (like in option 1), do I store the raw numbers in a "rate" attribute and then use my Python code to determine how to apply the numbers? or do I set up two tables which resemble something like a lookup table, one to store the rate values of A and the other to store the rate values of B and C (since they are both measured in seconds / part)
Is there a better way than these two options?
Top Answer
James
If I'm understanding your question, the keystone is this bit:
> I have a table "components". One of the attributes of this table is "type". "type" is restricted to only three values
You can use a foreign key constraint. In the design below, the components table will only accept a component type if it already exists in the component_types table.
if object_id('component_types') is not null begin drop table components drop table component_types end
create table component_types (comp_type_id int identity (0,1) primary key, description varchar(255))
insert into component_types (description)
values('Doodad'),
('widget'),
('gyrothingy')
select * from component_types
create table components (comp_id int identity (0,1) primary key, comp_type_id int,
foreign key(comp_type_id) references component_types(comp_type_id))
insert into components (comp_type_id)
values (0),
(1),
(2),
(2),
(1),
(0)
select * from components c
inner join component_types t on c.comp_type_id = t.comp_type_id
--this insert will fail
insert into components (comp_type_id)
values (3),
(4),
(5)
select * from component_types