or
Anonymous
sqlite
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
What is the best method to restrict certain entries from taking on certain values
Jack Douglas replying to Anonymous
OK, great, thanks for trying to help us help you! a fiddle isn't the only way but it can help illustrate a problem in language we all have in common.
Anonymous
I only mentioned sqlite and sqlAlchemy for full disclosure.
Anonymous
Yes, foreign keys are used in sqlite.  I'm thinking that I may not have made my question as clear as could be.  I do know that after looking at my problem, I did not touch the complexity of the issue.  It may simply be complex because I do not understand it well enough.  I will see if I can provide the db<>fiddle as requested.
Anonymous
Sorry for the delay in answering back.  So, my question is more of a general DB question.  Once I get the information, I can easily format it to  work in sqlite.  
James
https://sqlite.org/foreignkeys.html
James
I htink you can use foreign keys in sqlite
James
hmm I'm not sure, i've never used sqllite
Jack Douglas
@James is your answer SQL Server specific or is it easily adapted for SQLite?
Jack Douglas
Thanks for posting. If you can boil this down to a question about SQLite without the SqlAlchemy bits (and I don't know if that's possible or not), then I think it will get much more attention here — and if you can provide a [db<>fiddle ](https://test.dbfiddle.uk/?rdbms=sqlite_3.27) which helps explain the question, that would be even better.