sqlite add tag
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

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.